Forum Discussion
Dependent Dropdown List With Pivot Table
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.
kheldar Oh, my goodness. You link to an article from 2010. From earlier questions you have posted here, I deduced that you have MS365 or 2021, as you were using the LET function. So why not look into more recent tutorials on the subject that use FILTER and UNIQUE. Two of the newer and very powerful dynamic array functions.
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.
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
Hi Alex_Q
If you want them as rows instead of as columns, the logic has to be transformed as attached.
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.
- Man you are awesome. Thank you! I managed do it. I love this community. I started with absolute zero knowledge on excel formulas by all of your help I got a little ahead. I still have a lot to learn. Thanks again. cheers.
(Sorry for not responding earlier I don't know why but turns out I couldn't get the mail notifications work so I thought I didn't get a response)- In deed, here are lots of skilled and experienced people giving helping hands.
Thank you for your feedback 🙂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!
