Dropdown to select table then rows display infor from selected

[Hooty]

I have 4 tables created from 1 large table. The large table shows over 10,000 items, i added 4 columns to filter the data, Paving, Iowa, Nebraska, and Non-Dot. A Y/N, is in the 4 new columns, i used Microsoft Query to create:Table_Paving, Table_Non_DOT, Table_Nebraska, Table_Iowa. I am creating an estimation sheet for differing areas, but all the data is same/similar. i want to select the table to use, prior to selecting the item, price etc. I thought of using a single table, but i do not know how to use the 4 new columns as the filter. the attached file hasn't been completely filtered by Y/N so the tables only show test data (sheet1). any suggestions? internet search of: dropdown list to pick from 4 tables, didn't show what i think i need.

[ben_hensel]

I think you'd be better off just running off a single Pivot Table.

And use a single Helper for Location -- that can be "Nebraska" or "Iowa", and then filter for either (or both, or none, or some if you add more states).

See attached.

[Hooty]

ben_hensel,

Thank you for your help! i forgot to put the reason why i was looking to do it in VBA, or dropdown. attached is the base file for the table. The "Pricing" sheet is the base "Form" i'm creating for estimation,
the row marked yellow at beginning and end, will be the line that contains all the ways to get to the data. i have VBA to copy the row with formatting below. i will then filter the projected price/quantities
to the bid sheet, sent to customer (next step)

[Hooty]

I split the main list into 6 then used data validation with the following:

Please Login or Register  to view this content.

now i want the cell next to it to find the value in the 3rd column "average cost" based on the list selected. any ideas??