I am trying to develop a spreadsheet that can locate corresponding records in an external data source. So, let's say I have Column A with a list of identity values. I want to develop Column B, which perhaps shows a count of rows in the table with that value. Something like:
A B
758348 "=SELECT COUNT(*) FROM MYTABLE WHERE IDVALUE=$A$1"
173483 "=SELECT COUNT(*) FROM MYTABLE WHERE IDVALUE=$A$2"
... and so on. So, I thought I would use a parameterized query (where IDVALUE=?), but that prompts me to input the parameter value, not use the value from the cell to the left. Is there any way I can do this?
I would use a parameterized query (where IDVALUE=?), but that prompts me to input the parameter value
From what you mentioned I understand you are using MS Query and you need to follow the following steps
Steps to make a parameterized query in Excel use a cell value as parameter
Use the query wizard to create or edit queries
it is not very useful in most cases view
menu and uncheck tables
. This removes the graphically representation in query window and makes it easier to alter you sql query SQL
button below the format
menu in query window. A pop up comes where you can edit the query as you wish. Make sure to add question mark(s) for the parameter(s) you want. exit
button below the view
menu in query window. Then the query window closes and Import Data pop up comes asking where in excel to display the result. Choose accordinglyDefinition
tab. Click on Parameters
button in bottom of the pop up next to edit query
buttonParameters
pop up comes displaying the parameters used in the query. Here you must select the Get the value from following cell
radio button to select the value of cell as the parameter for the query. Click OK and you should be done.
This method is for people with no VBA experience. If you know VBA then refer to this answer to achieve the something similar using VBA.