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.

bosinander's avatar
bosinander
Iron Contributor
Jan 20, 2022
In deed, here are lots of skilled and experienced people giving helping hands.
Thank you for your feedback 🙂
m_dashed's avatar
m_dashed
Copper Contributor
Oct 27, 2022

bosinander 

 

I saw this thread as i'm looking for almost similar scenario. I would like to ask how to do this if the shift header is not a time related, just text but also duplicates. Thank you!

  • bosinander's avatar
    bosinander
    Iron Contributor
    Oct 27, 2022
    The shift header is not needed to be time. Can be text.
    Duplicates will give you problem since it is not possible to differ if you first or second instance of same text is chosen. And even if it was solved in the model, maybe it could be confusing for a user not knowing the difference between first and second duplicate..?
    • m_dashed's avatar
      m_dashed
      Copper Contributor
      Oct 27, 2022

      bosinander 

       

      Thank you for the reply. got an error #value! 

       

      Unfortunately I cannot upload. This is the formula from your file:

       

      =LET(data,UNIQUE(FILTER(Table1[Shift],Table1[Shift]<>0)),
      HOUR(data) & ":" & TEXT(MINUTE(data),"00")
      )

      This is the table

      ShiftGroupName
      TextVideo 1Employee 1
      8:00Video 1Employee 2
      8:00Video 2Employee 3

       

      Data validation: but i can choose "8:00" and give me the correct data validation but i cannot choose the "text" on the Shift column

      ShiftGroupName
      #VALUE!#VALUE!#VALUE!

       

       

       

      • bosinander's avatar
        bosinander
        Iron Contributor
        Nov 10, 2022

        m_dashed You are of corse correct. The former attached example expected and parsed times in a way that gave you the error.

        The now atteched file is more generic and will do it with text.
        Duplicates are welcome in all of the three categories.

        G6:i9 can be moved to another sheet since it is only help calculations. Cut and paste if you want.

         

         

Resources