Truly Dynamic Dropdown Lists?

[BrianGGG]

Hello,

I have been scouring the internet and these forums to see if I can find a truly dynamic scheme for dropdowns. I have seen lots of solutions that depend on many named ranges, but I am hoping for more of a tabular approach.

Here is the situation:

I have a single table that has a group and an item similar to:

Group Item
Ggp1 Item1
Ggp1 Item2
Ggp1 Item3
Ggp2 Item4
Ggp2 Item5
etc.

(there could be 10 groups and 8-10 items per group)


Then, I have a data entry table with two fields: Group and Item.
What I would like to do is:

• First choose the Group name from a dropdown. This would be the distinct groups from the codelist.
• In the field to the right of the group (the item) dropdown only the items that match the chosen group.
• I would like to avoid using a separate named range for every group because I would like to add new groups into the codelist above without having to create new named ranges
• I tried deriving a range in the table by figuring out the first and last instance of each group and then doing INDEXES. No good with Data Validation, it kept saying invalid formula.
• I tried INDIRECT and OFFSET to the best of my abilities, that doesn't seem to work either
• I CAN sort the codelist, I could use helper columns/tables with the codelist or the data table, I CANNOT use VBA

Anyone have any ideas about whether this can be done truly dynamically without any pre-defined named ranges?




thanks
BrianGGG

 

[Dr. Demento]

Addendum: Another contribution by Jon Acampora in the area of data validation drop-down lists - How to Search Data Validation Drop-down Lists in Excel

Nice!!!