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.
- Thank you for pointing that out. It's embarrassing that I didn't notice it.
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.
- 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 🙂
