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

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