Beyond Excel: Parameterized Query

Create a Parameterized Query in Excel without Add-ins, Macros, or Formulas

---

Closed Caption:

yeah
and
giving users the ability to extract zip
data into excel doesn't have to involve
add-ins vba or even formulas in this
post will learn how using a
parameterised weird for purposes of
illustration we'll use an Excel workbook
as a data source it contains a named
range with data downloaded from the US
census bureau the data doesn't have to
be in an Excel workbook as this method
works with access sequel server oracle
DB to and other databases to prepare our
worksheet will set up to input cells for
our parameters and this case will be
searching the census data by state code
and county named for aesthetics will
format the input labels with an accent
style and our input cells with an input
style now we can link our Excel workbook
to the census data using menu option
data from other sources from Microsoft
query make sure you use the query wizard
is unchecked then select data source
excel files
next we browse to our data source and
select the named range containing census
data microsoft query displays a field
box where we can select all fields by
double-clicking the astrix microsoft we
reload the field data into the data pane
our next step is to add our state and
county criteria click the show/hide
criteria icon to bring up the criteria
display select state from the criteria
field drop-down in the value field type
brackets to indicate we want state be a
dynamic parameter to test our parameter
will use pennsylvania state code PA
note the data pain now only includes
Pennsylvania reckons select name from
the next drop-down this time we'll use
the like keyword with our two brackets
and a % indicate this is a wild-card
enabled parameter to test this we will
type C in the parameter box and get all
pennsylvania counties beginning with C
click the return data icon to close
Microsoft weary and return to excel
excel presents the import data dialog
click properties in the connection
properties dialog click the definition
tab click parameters set parameter 1 to
get the value from our state input cell
and when that cell changes automatically
reload our data with records matching
set parameter to to get the value from
our county input cell and when it
changes reload our data click OK to
close the parameters dialog click OK to
close the connection properties dialog
select the upper cell where we want our
data to display and click ok to close
the import data dialog that's it
we now have a parameter eyes query
pulling data from an external data
source without using any add-ins
formulas for macros
yeah
yeah
yeah
yeah
yeah