How to use parameterized query in Excel using column as parameter?

by

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

  1. First go to the Data tab in Excel and Select MS Query under External Data Sources

MS Query

  1. A pop up appears asking you to choose data source. Select the data source or add a new data source and select it. Make sure to uncheck Use the query wizard to create or edit queries it is not very useful in most cases

Data Source

  1. Then a pop up appears asking you to add table select any table for now. It is better to select a table with less rows.

add table

  1. Then a Query window appears with the table you have selected and its data

query window

  1. Then click on the view menu and uncheck tables. This removes the graphically representation in query window and makes it easier to alter you sql query

View menu

  1. Click on the 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.

edit query

  1. Then a pop up appears ask you for parameter value(s) enter some valid value for now

parameter

  1. Then the query window shows the result of query based on the parameter entered

result

  1. Click on the 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 accordingly

import

  1. Then click on Properties button on left before you click OK. A new pop up appears with the default usage tab selected.

Properties

  1. Make any changes needed in this tab and click on Definition tab. Click on Parameters button in bottom of the pop up next to edit query button

Definition

  1. Parameters 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.

    parameter value

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.

More Related Contents:

  1. how should I apply a formula and get answer in all the odd cells?
  2. Sequence of numbers based on start and end value of 2 cells [closed]
  3. Vlookup using 2 columns to reference another
  4. How to insert a picture into Excel at a specified cell position with VBA
  5. Save multiple sheets to .pdf
  6. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  7. How can I create a calendar input in VBA Excel?
  8. Using Google Chrome in Selenium VBA (Installation Steps)
  9. Excel formula to get cell color [duplicate]
  10. Ignore Duplicates and Create New List of Unique Values in Excel
  11. Merge values of column B based on common values on column A
  12. How to import a table from web page (with “div class”) to excel?
  13. Range limit conundrum
  14. Using LocationTextExtractionStrategy in itextSharp for text coordinate
  15. How do you run a .exe with parameters using vba’s shell()?
  16. How to determine the last Row used in VBA including blank spaces in between [duplicate]
  17. Passing variable from Form to Module in VBA
  18. How to give a time delay of less than one second in excel vba?
  19. Changing the date format to yyyy-mm-dd
  20. If two cells match, return value from third
  21. Create an Excel File (.xlsx) using PL/SQL
  22. Delete all duplicate rows Excel vba
  23. excel to pdf ignores print area and calls printer
  24. Leave out quotes when copying from cell
  25. Select Newest Record and Create New Table of Unique Values in Excel
  26. Converting a string representation of a constant into a constant?
  27. sep=”;” statement breaks utf8 BOM in CSV file which is generated by XSL
  28. Write from R into template in excel while preserving formatting
  29. Can Excel interpret the URLs in my CSV as hyperlinks?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment