excelxp autofilter - use as a drop down value selection box

[billinAZ]

i would like to create a spreadsheet that has a column that acts as a drop down selection value (for example: States). Users would be able to click on the drop down menu, select the state they want and that would populate the cell in the spreadsheet.
Currently I use autofilter which does give the dropdown capability, but also requires that each row of my spreadsheet already have a value (one of the states)...
has anyone done this? is it possible to have autofilter variable contain a list of states but not actually fill the cell until the user selects one of the values?

here is my code (using a format to try to blank out values for state in each row--doesn't work)

proc format ;
value $vickie
low - high = '';
run;


**This is from SAS NOTE 38195: Apply filters to columns in the output created with the ExcelXP destination";

ods listing close;

*ods tagsets.excelxp file='filters.xls' style=statistical
options(autofilter='all');
ods tagsets.excelxp file='filters.xls' style=statistical
options(autofilter='4' sheet_name='Visit 1' absolute_column_width='15' Frozen_rowheaders='1' FROZEN_HEADERS='1' );
proc print data=&xlsname noobs;
var Name type length state;
format state $vickie.;
run;

ods tagsets.excelxp close;

ods listing;
run;

[Cynthia_sas]

Hi:
The autofilter capability is something that TAGSETS.EXCELXP is turning on -- it is designed (by Microsoft) to filter the data that is -already- in a spreadsheet.
(see: http://www.wikihow.com/Use-AutoFilter-in-MS-Excel )

To me, it almost sounds like you are describing how the stored process interface works in the SAS Add-in for Microsoft Office. A stored process will take country (or state) as a parameter. The stored process is defined to hold that parameter. When the user (in Excel) runs the stored process, the first thing they see is a prompt window that asks them to pick the Country (or State). Then they click a button (OK) to run the stored process and the results for the state they wanted are returned to Excel in spreadsheet form.

This behavior is very like an HTML form - -where the user calls up a web page that is a form -- the form collects information -- maybe with checkboxes, radio boxes or drop down selection boxes. The user makes choices and clicks a button and then the program -behind- the HTML form will go get or do whatever the program was supposed do and the results are returned to the user's browser in the form of an HTML page.

I do not believe you can use TAGSETS.EXCELXP to build a form in the way you envision. Autofilter is meant to be turned on IF you want to filter the data or procedure results that ODS is putting into the spreadsheet. So your trick of blanking out the state name and then thinking that autofilter will "reveal" the states that are filtered just isn't how autofilter works.

Excel/Office have some Microsoft-based technologies that you might want to investigate:
1) forms or data entry forms
2) web query
3) Excel macro, VBA program, VB script or ActiveX program that solicits the state name and then opens the ExcelXP-created file and creates a new sheet or workbook based on extracting rows from the workbook with everything. Here are some links to get you started.
http://www.siop.org/tip/backissues/April%2004/pdf/414_061to069.pdf
http://www.ehow.com/how_4473738_create-forms-using-excel.html
http://office.microsoft.com/en-us/excel-help/creating-a-data-entry-form-with-the-excel-template-wiza...
http://support.microsoft.com/kb/157482
Wittwer, J.W., "Excel Web Query Secrets Revealed" From Vertex42.com, 2003, http://www.vertex42.com/News/excel-web-query.html
http://www.ozgrid.com/VBA/inputbox.htm
http://en.allexperts.com/q/Excel-1059/2008/3/prompt-user-enter-date.htm
http://excel.tips.net/Pages/T002266_Getting_User_Input_in_a_Dialog_Box.html
http://www.eggheadcafe.com/software/aspnet/33967973/copy-to-another-workbook.aspx


If you have the SAS Enterprise Intelligence Platform (also known as the BI Platform) and the SAS Add-in to Microsoft Office, then I would recommend a different approach. But since you said you were using TAGSETS.EXCELXP as your destination, I figure you are using Base SAS.

cynthia