MS query parameter data from excel 2007 worksheet cell

Question

Hello all, I am looking to build a MS Query from the Query Wizard & ODBC.  I need it to use the values in a specific worksheet.  I am able to prompt myself using [ ], but not able to figure out how to make it pull from lets say Sheet1!F11. We are currently hard coding the values into the SQL statement.  I would like to build a new one (query) with the drop down providing the different criteria, and then another query to pull the search criteria from the drop down and use the "value" in the returned field from query 1.  Any help would be wonderful, I have a head ache from beating my head on the desk.

Answer 1

I'm talking about right-clicking the results of the query in the Excel worksheet.

Answer 2

In using MS Query, I am not able to right click on any table, or any other item for that matter.  I tried to build a new query and pulled in items from different tables and was not able to right click on any of them.  Where/what developer are you working with? I am new at this and it has been years since I have built larger sql statements.  If you know the sql statement which enables you to pull from the table a copy of the text in the statement would be a way to show it as well, if it shows up in the sql statement.

Thank you very much!

Doug

Answer 3

Having got a querytable with a prompt on refresh, right-click the querytable > Table > Parameters > Get the value from the following cell: Sheet1!F11, and decide whether you want it to automatically refresh when the cell value changes.