Data Validation List to Return Variable/Dynamic Data based on Reference Table

[dilbert1865]

Hi,

I'd like to create a table that will return dynamic values for the headers based on a data validation list (drop down list). I've attached a small scaled sample spreadsheet of what I'm trying to do.

- The first tab (Finished Goods Map) is a matrix that shows a listing of items and the components that make each item; the relationship is confirmed via an "X"

- The second tab (Tables) is a listing of values based on supplier capacity, (internal) manufacturing capacity and sales demand.

- The last tab (Item Listing) shows what I'm using now and what I would like to do; there's more detail in the file. The top third of the page shows what I'm doing now, the middle third shows what I would like my information to look like (the presentation) and the last third is just a listing of each component that makes an item and what the headers would look like.

I've thought about how to do this for a little while, and I cannot for the life of me even think of the formulas to make this work, but I would like to believe that this is possible without needing VBA. I'm also open to alternative ideas too (i.e. If I change how my information is laid out to create a pivot table, would that be better?)

Thanks!

[hemesh]

Hello Dilbert find attached

is this what you are looking for ?

formulas in finished goods map(Third listing) is array formula ! So needed to be confirmed with control shift and enter !

formula in middle part uses named ranges(to check named ranges go to formulas---> name manager).

where you wanted to use data validation you haven't mentioned here In the case you wanted to use data validation for items component in the middle part then you can use last table as named range and use indirect formula to refer component

[kvsrinivasamurthy]

@ hemesh,
In Sheet Item listings In B16 use this and drag across.

Please Login or Register  to view this content.

SelectA16
Data validation - Settings
Allow - List
Source =$A$25:$A$29

[dilbert1865]

YES! That's it! Thank you so much guys! I'll mark this as solved!

[hemesh]

Thanks for the feedback !