Log In
Register
Home
Active Member
March 2, 2023
Offline
Hi Team,
I´m having issue with dynamic drop-down menus accessing the list through named ranges, via indirect formula. Your information on Ignore Blanks in Excel Data Validation List got me moving on, but now I'm stuck. Kindly check attached file for more info.
Thank you very much!
Liburno
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Is there any reason to why want to use a range instead of a table for your data? I would simplify your approach by
- creating an excel table for each columns of data
- avoid having empty table cells
- change the references in the name manager to use structured references
ex:
=Table1[Fruit]instead of
=Tabelle1!$F$4:INDEX(Tabelle1!$F$4:$F$10;SUMPRODUCT(--(Tabelle1!$F$4:$F$10<>"")))
You will notice that your drop down lists in O5 to O7 will work as wanted.
Br,
Anders
Active Member
March 2, 2023
Offline
Hi Anders,
thanks so much for stepping in!
So, as expected, your sollution works! The thing is that it doesn't suit me.
Here is the thing: the user check-marks one or more titles (groups of related products), and the formula assembles the list(s) of products corresponding to the check-marked title. These resulting lists are standing next to each other and are the source of drop-down menu data for the next step in configurator.
However, when the check-box is e.g. un-ticked the resulting list disappears and the one next to it gets it's place. Additionally, these assembled lists are not equally long, meaning if I set the table for them in advance, the drop-down menus will have empty fields (or even lleave out some items).
When working on this I noticed that in Name-manager I have no specific value when using ranges instead of tables (image attached). There is a reason for it, but - could this be the source of the issue?
Thank you and BR,
Leo
1 Guest(s)
