Forum Discussion

kheldar's avatar
kheldar
Iron Contributor
Dec 31, 2021
Solved

Dependent Dropdown List With Pivot Table

Hello,   I've got a set of data which will be dynamic so I created a pivot table to be able to sort the data and create a dependent drop down list.   I need to create a 3 level drop down list.  ...
excel
office 365
  • bosinander's avatar
    bosinander
    Jan 02, 2022

    Hi @kheldar 

    As Riny_van_Eekelen mentioned, using Excel 365 it is possible with FILTER and UNIQUE without using a pivot table.

    The attached example is functional from Shift to Name where eg the formula in i9 results in the drop down list that is used in i2.

     

    =UNIQUE(FILTER(Table1[Name];(--(Table1[Shift]=--$G2))*--(Table1[Group]=$H2)))

     

    G9 concatenates hour with minutes to make it look like time. TEXT() is not generic functional since h:mm is translated into eg t:mm in swedish Windows.

    HOUR(data) & ":" & TEXT(MINUTE(data);"00")

    --G2 makes Excel treat the string as a number again.

     

    -/-

    G8:i9 may be cut and pasted on another sheet.

Alex_Q's avatar
Alex_Q
Copper Contributor
Apr 26, 2023

bosinander 

 

How do use this with mutilple drop lines?  I tried to modified but since the expandable list points back to the the drop down I could not make it point to the new drop down.

thanks

  • bosinander's avatar
    bosinander
    Iron Contributor
    May 20, 2023

    A more generic solution for two levels with the hierarchy set up in the gray table Options, parsed in G3#:G4.

    Data Validation formulas in the orange table are =Group# and =item_opt.

     

    Defined names
    Group=Sheet1!$G$3
    item=Sheet1!$G$4
    item_opt=INDEX(Group#:Item,2,XMATCH(Sheet1!F16,Group#))#
      
    Formulas
    G3=TRANSPOSE(UNIQUE(Options[Group]))
    G4 →=FILTER(Options[Item],Options[Group]=G3)

     

    Row 4 beyond J4 is preparation for more groups.

     

    The three parts - gray table, background calculations G3#:item and orange table - may if plausible be on same or different sheets.

Resources