Hey everyone,



I am building a template that consists of only 3 tabs.

One is the raw data tab, titled: Data
One is the list of products I have that feeds the Brand Input tab, titled: Primary Products
One is where I need help (marked in red), titled: Brand Input

I made a comment in the spreadsheet of what I am trying to do. Essentially, if you are in the "selection" column on the "Brand Input" tab, the user should see a list of all bathroom sinks from the data tab for example.

I am going to continue to update the data tab with additional bathroom sinks (example) and I am trying to find a way to quickly update the list once I add more information.

Is it as simple as using a Pivot Table and then just keep refreshing the data? How do I get them in a list format in a pivot table?

Longing for some help...

Thanks a lot.

Attached Files

Capex Selection Tool 07.06.15 CF.xlsm (213.8 KB, 9 views)

Hi, cfreezy,
Welcome to the forum

You can set a pivot table with Product and description only, both fields in Row field of the pivot table; the most important setting is to set the report layout to be displayed in tabular form, and to repeat row labels.
Then, you can use this relatively simple offset formula:
=OFFSET(Sheet1!$B$1,MATCH(B12,Sheet1!A:A,0)-1,0,COUNTIF(Sheet1!A:A,B12))
You can add data to the data table, when you refresh the pivot table, the new products will show in the dropdowns.
See the file attached.
Cheers,
Catalin

Attached Files

Copy of Capex Selection Tool 07.06.15 CF.xlsm (232.9 KB, 11 views)