Forum Discussion

RockB115's avatar
RockB115
Copper Contributor
Dec 23, 2020
Solved

Using named range with FILTER function in data validation list (dynamic dropdown)

Hello,   I am trying to use a named range as a data validation list but unsuccessfull do far. The named range is created using Excel's Name manager under Formulas Tab. The named range has a formula...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 23, 2020

    RockB115 

    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.

     

Oken .'s avatar
Oken .
Copper Contributor
Jul 10, 2021

RockB115you can use the OFFSET approach. I find it pretty useful when the workbook is not very large. See screenshot of the formula below:

lstEmpNameLT:

=OFFSET(Employees[[#Headers],[Employee Names]],
MATCH(LeaveTracker[@Unit]&LeaveTracker[@Area],Employees[Unit]&Employees[Area],0),,
SUM(--(Employees[Unit]&Employees[Area]=LeaveTracker[@Unit]&LeaveTracker[@Area])),)

The formula creates an array of Employee names based on Unit & Area (Concatenated).

See the file attached for more details. With this you have a three-level cascaded drop-down.

 

Resources