Forum Discussion
Using named range with FILTER function in data validation list (dynamic dropdown)
That's small sample file
Helper range expands automatically if you change the table except spills in column N shall be drag down. Data validation formula is
=XLOOKUP($H4&$I4,$L$9:L10&$M$9:$M10,$N$9:$N10)#It is expandable if take care about absolute/relative references.
Just mock-up, perhaps it could be done more elegant.
Riny_van_Eekelen I tried this method, but it only works for single row. If you look at my table "Leave Tracker", I need the dynamic drop down to work for all rows of the table. Unfortunately, the dropdown for "Employee Name" column for rows 2 and beyond wont change irrespective of selections in "Unit" and "Area" columns. Only the first row is functional.
Any suggestions on how this can be implemented to multiple rows?
Drop-down list works with ranges, not with arrays. First in mind is to create such helper range somewhere outside - spill two columns from the table, add transpose filters to each row and INDEX()# them in drop-down list.
- Drop down lists actually work with arrays. The OFFSET() + MATCH()+COUNTIF works well in this case. It creates an array of the items you want to list and you can use them pretty much in the dropdown.
OFFSET() returns reference on dynamically defined range, not an array.
SergeiBaklanThanks for the clarification. My bad. I was thinking of something else.
SergeiBaklan But does this work for multiple rows?
That's small sample file
Helper range expands automatically if you change the table except spills in column N shall be drag down. Data validation formula is
=XLOOKUP($H4&$I4,$L$9:L10&$M$9:$M10,$N$9:$N10)#It is expandable if take care about absolute/relative references.
Just mock-up, perhaps it could be done more elegant.
SergeiBaklan Thanks! This solution works. I was looking to avoid VBA since I was planning to upload this file to Onedrive and use Excel Online. This solution just made this possible.
One very small correction in your XLOOKUP formula was to change "L10 to $L10"
Also, the helper table
=Table2[[Unit]:[Area]]is dynamic but the FILTER formula on the adjacent cell (N9) didn't update as size of helper table increased.
=TRANSPOSE(FILTER(Table1[Name],(Table1[Unit]=L9)*(Table1[Area]=M9)))However, it was an easy fix. I just dragged down the formula to fill the entire N column. I suspect this somehow made my excel workbook slower. Saving takes a little more time now. But should be OK and everything works as excepted. Thanks Again!
