Forum Discussion
Berni_el_0rnitorrinco
Jan 16, 2025Copper Contributor
Struggeling with multiple value drop down list - Indirect function
Hello!
I work in a Warehouse, and we decided to move from Google Sheets to Excel web. I spend all day trying to figure out how to create drop down lists, but I got stuck.
We have a three column classification, Group, Subgroup and Item, and all of this classification are related, so an Item is allocated inside and specific Subgroup, and this Subgroup to a specific Group. Even some Subgroups have the same name, but they belong to different Groups, sorry if this is messy.
I managed to create the drop-down list for the Group, and the Subgroup but when it comes to the Item the Data validation doesn't allow me to introduce the right formula.
For the Group I'm using a simple reference to a table with the different groups, for the Subgroup I'm using =INDIRECT(SUBSTITUTE($A2;" ";"_")), but for the Item I think I should use =INDIRECT(SUBSTITUTE($A2&"_"&$B2;" ";"_")) but it's not working...
Maybe I'm overthinking this, and it's much easier than what I'm trying, but I'm complete stuck not understanding why does not work...
Also, I have created a specific table for each of the items of each Group, and Sub-Group.
I'm adding a link to the web excel if someone wants to look it up for a better understanding of the trouble: Inventory
Thanks in advance!
- Berni_el_0rnitorrincoCopper Contributor
Wow! Where was the problem? Why couldn't I make it work?
Many thanks!! True savior!Your idea was correct. However. you used =$F$16 or something like that as formula in the data validation rule. The value of that cell was text. I simply changed the data validation formula to =INDIRECT(SUBSTITUTE(A2 & " " & B2, " ", "_"))