Excel Spreadsheet as the Source of a Query Prompt

 

Problem Statement:

How do I use an Excel spreadsheet as the source of a Query prompt?

Solution:

Follow detailed steps below:

1. The worksheet to be used as the source of a prompt should be saved as an Excel 4.0 or higher workbook (*.xls).
   NOTE: Omit column headings via Tools - Options - File Output.
    
2. When in the Query conditions dialog box, select the desired column and condition type, and select prompt.
3. In prompt attributes, drop down on the Source of prompt Answers and select List of Values from PC File.
4. Click on source details, and dialog box for Prompt Answers From File appears. Under Name Of File Containing Values, select Browse, and change the Files of Type to Excel (*.xls,*xlsx).
5. Browse to the correct workbook and double click to select.
6. Under Value Column Number, specify which column has the values to be used.
7. Select OK twice.
8. Select Apply
9. Run the Query

Ref#: 1475430