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.
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!
Yes, FILTER() shall be dragged down if the table is expanded. On the other hand you may drag it with some gap from very beginning. Filter against empty columns shall return an error, I guess CALC. You may wrap formula with IFERROR or simply ignore errors, in helper sheet they affect nothing.
Performance - I don't think it'll be significant impact, approximately the same as you add one more calculated column into your main table.