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

 

[Eric W]

Sure, consider the following layout:

	A	B	C	D	E	F	G	H
Group	Matching Items	Group	Item	Groups
Grp2	Item4	Grp1	Item1	Grp1
Item5	Grp1	Item2	Grp2
	Grp1	Item3
	Grp2	Item4
	Grp2	Item5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=IF(B1="","",IFERROR(INDEX($F$2:$F$11,SMALL(IF($E$2:$E$11=$A$2,ROW($E$2:$E$11)-ROW($E$2)+1),ROWS($B$2:$B2))),""))&""}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]{=IF(H1="","",IFERROR(INDEX($E$2:$E$11,SMALL(IF($E$2:$E$11<>"",IF(MATCH($E$2:$E$11,$E$2:$E$11,0)=ROW($E$2:$E$11)-ROW($E$2)+1,ROW($E$2:$E$11)-ROW($E$2)+1)),ROWS($H$1:$H1))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Your table is in columns E:F. In column H, we need to have a formula to find the unique entries from column E. Put the formula in H2, confirm with Control+Shift+Enter, then copy it down the column as far as needed.

Now select to A2. Click Data Validation, select List, and enter this in the Source: box:
=OFFSET(H2,0,0,COUNTA(H:H)-1)

and click OK. This points to the list generated in H.

Now enter the B2 formula, confirm with Control+Shift+Enter, and copy down as far as needed. Note that the headings in B1 and H1 are required.

Now you can select the group you want from A2, and the list in B2:Bx will be a list of matching items.

Let me know if this works for you.

 

[Dr. Demento]

I'm not certain if this would meet your criteria -- How to Create Dependent Drop-down Lists.

 

[BrianGGG]

Hi Dr Demento. I tried to follow this link, but it points to a topic unrelated to dropdowns. Can you re-post the link?

thanks
BrianGGG

 

[BrianGGG]

Eric W,

Thanks for this post. It gives me hope that solving my problem is possible. I just have one additional requirement.

I was able to line up all of the formulas and both of the array formulas in your example above, and they worked perfectly.
There's just one missing piece, and granted it might be the hardest piece.

In your example above, the combination of the derived table and the array formula creates a truly dynamic dropdown based on the codelist table as I was hoping for.
However, next two the first dropdown, all of the items are listed sequentially in the rows below, e.g. Item1 in row 2, Item2 in row 3, etc.

What I am hoping to do is use the dropdown exactly like you have shown me in Column A to get the group number. However, once the group is chosen, I would like to have a second dropdown that only lists the items that match the chosen group in the column to the left of it.

So, visually it would be:

Group1 (dropdown) Items for Group1 (dropdown)

I can't figure out a way to adapt the formula in column B into a data validation for a list that will be accepted by Excel.
Is something like this possible?

I was also trying to figure out if I could adapt the logic for creating a unique group to creating separate columns for each group's items. But I couldn't figure out how I would then convince Excel to pick the right column based on which group is chosen.

Any ideas here?


Thanks
BrianGGG

 

[AliGW]

Hi Dr Demento. I tried to follow this link, but it points to a topic unrelated to dropdowns. Can you re-post the link?

thanks
BrianGGG

It's here: http://www.contextures.com/xlDataVal02.html

 

[Dr. Demento]

Thank you, AliGW! Further evidence that I suck at multi-tasking

Here's where I intended to point you (tested this time!) -- https://www.excelcampus.com/tables/dependent-drop-lists/

The one thing I liked about Jon's explanation is that he highlights how to use structured references (which I'm always a fan of) in this context.

FWIW, Jon's solution (in the link above) allows for what you're looking to accomplish. I just created three dropdown columns that depending on the the column to the left, only shows "pertinent" choices.

Cheers!

 

[Eric W]

Just move the B2 formula to I2, and copy down. Header required in I1. Then in B2, click on Data Validation > List, and this formula:

=OFFSET(I2,0,0,COUNTA(I:I)-1)

 

[BrianGGG]

Thanks all. This was a group effort. The technique described by John A in Excel Campus provides the right technique for dependent dropdowns using Excel tables as codelists. Eric W's method for creating a unique list as a "side-table" saves the hassle of having to maintain more than one table.

Much appreciated to everyone that answered above!


Regards,
BrianGGG

 

[Eric W]

Glad we could help. Synergy is truly a wonderful thing!