Oct 05 2021 04:32 AM
Dear colleagues,
It turns out that I am to build a report based on drop-down options. For that purpose I am nesting IF formulas as shown below:
The problem is that I really need a huge amount of operators for it to work as intended. Suffice it to say that I have 50 different values for "E2" and depending on this "E2" I might have at least 5-10 dependent values for the drop-down menu to display. I tried to write the nested formula but I get to a point where I can't enter any further character. I think this stems from Excel's character limitation per cell. Does anyone know how to get around it?
Thanks a lot!
Oct 05 2021 04:53 AM
@Asa94 Since you tagged your post with Office365 I assume you can use dynamic array functions like UNIQUE and FILTER. Rather than "programming" all options in the List box, create a table with all possible selections. Then for each next level drop-down you can filter unique options from that table and point the data validation list to a dynamically filtered array. A simplified example is included in the attached file.
Oct 05 2021 06:38 AM
@Riny_van_Eekelen Yes, I am using O365, however I'm quite a newbie in Excel.
I tried to use your array formulas but got an error upon typing them, for instance, I cannot use the tblSelection[] part since it triggers a reading error. I tried to do it stating the exact cells instead but it didn't work either. PFA my attempt. Hope you can shed some light on this :)
Thank you!
Oct 05 2021 07:38 AM
SolutionOct 05 2021 08:18 AM
@Riny_van_Eekelen thanks a lot for your outstanding patience and support. Will definitely learn more about dynamic arrays.
All the best,
Oct 08 2021 03:31 AM
In the end, I managed to implement it. However, whenever I try to extend the drop-down list to other rows, the options are still anchored to the first row option, in my case =UNIQUE(FILTER(OrgChart[Sub-Area],OrgChart[Area]=Report!D2)). This shouldn't happen:
Do you happen to know how can I do to make it work? My need is basically to make each independent - so its drop-down list is updated nas per each "Area" value, not based upon the first designated output cell. The first thing that came to mind is to generate a dynamic array for each row, but it would be to hard and time-consuming.
Thanks a lot for your support!.
Oct 08 2021 03:42 AM
@Asa94 Can you upload a file? Difficult to help on the basis of a screenshot and a formula without seeing it all at work.
Oct 08 2021 05:01 AM
@Riny_van_Eekelen Sure, here you go :)
Oct 08 2021 05:26 AM
@Asa94 Okay! You basically need to create a dynamic references for every row. The video in the link below contains the explanation.
Oct 08 2021 05:49 AM
Oct 08 2021 06:08 AM
@Asa94 Not that I know! Sorry.
Oct 05 2021 07:38 AM
Solution