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