Copy and paste into cell A1
First, we’re going to turn our data into a data table so we can reference it with named ranges. This will allow things to update automatically when we add data to our table.
Creating a data table will automatically give the data a named range (something like Table1), so we’ll change the name to something more meaningful next.
Now we will add an extra column into our data table.
Now we will add a data connection to query our data table and return only the rows of data with Include = Y (i.e. only the rows of data pertaining to a given customer). Before doing this save your workbook.
Select this workbook from wherever you saved it and hit the OK button.
Now we will create our query.
The next screen will allow you to query only the rows pertaining to the customer selected.
The next screen will allow you to order your query results. For our purposes we don’t need to do this, but it may be helpful to see the orders listed in ascending order in the drop down list we’ll make later so we can add that here. Otherwise, click the Next button.
Now choose where your new queried data will appear in your workbook. The resulting data will be named something like “Table_Query_from_Excel_Files”, rename this to “Query” using the same method that we renamed our original set of data with.
Now let us create the order drop down list.
Now when we change the customer and refresh our query, the order drop down list will update with the relevant items.
