Forum Discussion
Data Validation: How to get around character limitation in the "Source" field
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!
- 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.
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!
- 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.
