SOLVED

Data Validation: How to get around character limitation in the "Source" field

Copper Contributor

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:

 

Asa94_0-1633433204484.png

 

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!

10 Replies

@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.

@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!

best response confirmed by Asa94 (Copper Contributor)
Solution
@Asa94 Didn't want to assume you were a beginner. But now that you mention it, I would recommend you to start learning about structured tables. The one I used in the example was called "tblSelections". In the attached file I just called it "Table1". What seems to be complicated referencing with @ and [ ]  goes all automatic when you click on a cell or select an entire column in a such a table. Just try, see what happens and start over when it goes wrong.
 
Learning the dynamic array functions is also a good thing. Once you get the hang of them they are soooo much easier to work with than "old school" functions.
 
In summary. First create table (the blue one in the attached workbook, called "Table1") with all the possible selections. Then designate the cells where you want the dropdown to be (I2:K2 in the attached workbook). Now you can create the dynamic arrays using UNIQUE and FILTER as demonstrated in columns E, F and G, linking to the cells I2, J2 or K2. Finally, set up data validation in these cells and enter the reference to the first cell in the relevant array followed by a #.
 
Perhaps intimidating in the beginning, but you'll find many resources on line that can help you much better that I can. Google does wonders.

@Riny_van_Eekelen thanks a lot for your outstanding patience and support. Will definitely learn more about dynamic arrays. 

 

All the best,

@Riny_van_Eekelen 

 

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:

 

Asa94_0-1633689017727.png

 

 

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!.

@Asa94 Can you upload a file? Difficult to help on the basis of a screenshot and a formula without seeing it all at work.

@Asa94 Okay! You basically need to create a dynamic references for every row. The video in the link below contains the explanation.

https://www.youtube.com/watch?v=R2PjPhXGjJU 

Thanks! I had a look at the video and that's what I aimed to do at first. However, as you'll imagine, having to set data validation for each cell and each row, not to mention creating the array for each cell, it far too much time-consuming and it entails that the person whoch is actually populating the report sheet with data needs to know how do arrays work and do this continually. Is there any way to automate these data validation and array creation tasks? Thanks a lot!
1 best response

Accepted Solutions
best response confirmed by Asa94 (Copper Contributor)
Solution
@Asa94 Didn't want to assume you were a beginner. But now that you mention it, I would recommend you to start learning about structured tables. The one I used in the example was called "tblSelections". In the attached file I just called it "Table1". What seems to be complicated referencing with @ and [ ]  goes all automatic when you click on a cell or select an entire column in a such a table. Just try, see what happens and start over when it goes wrong.
 
Learning the dynamic array functions is also a good thing. Once you get the hang of them they are soooo much easier to work with than "old school" functions.
 
In summary. First create table (the blue one in the attached workbook, called "Table1") with all the possible selections. Then designate the cells where you want the dropdown to be (I2:K2 in the attached workbook). Now you can create the dynamic arrays using UNIQUE and FILTER as demonstrated in columns E, F and G, linking to the cells I2, J2 or K2. Finally, set up data validation in these cells and enter the reference to the first cell in the relevant array followed by a #.
 
Perhaps intimidating in the beginning, but you'll find many resources on line that can help you much better that I can. Google does wonders.

View solution in original post