Hi,
I would like to ask your help to create a dynamic and dependent drop down list with unique values based on table header.
Please, see the details and the named formulas in the attached sample file!
Note: the orignal file is much bigger, therefore I would like to avoid the time consuming array formula(s).
Thank you for taking the time!
This is what I came up with. I needed a whole helper sheet called Lookups.
The easiest way to get a unique list of values from a data set is to use a pivot table. I did this for each of the categories on the Lookup sheet. I also have a small table that counts the number of items for each category and also records the category's offset from the leftmost column. These are two key pieces of the Offset command.
=OFFSET(Lookups!$A$4,0,VLOOKUP($B9,Lookups!$E$2:$G$4,3,FALSE),VLOOKUP($B9,Lookups!$E$2:$G$4,2,FALSE),1)
When you change data in the table, you will have to refresh the pivot table.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
@dflak Thank very much the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks