Forum Discussion
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.
Shift > Group > Name
I tried to follow this tutorial but couldn't implement it to suit my needs.
https://contexturesblog.com/archives/2010/03/31/dependent-data-validation-from-pivot-tables/
I'm attaching a sample file. Since the data source is dynamic there will be blank values in between that are inevitably included in the pivot.
These are the name ranges I created but couldn't get it to work.
Shift: ='Group List'!$I$2:$I$6
ShiftGroup: ='Group List'!$E$2:$E$79
GroupHeader: =OFFSET(ShiftGroup,-1,1,1,1)
ShiftGroupNo: =MATCH('Group List'!$I$11,ShiftGroup,0)
ShiftGroupNext: =MATCH(INDEX(Shift,ShiftNo+1),ShiftGroup,0)
ShiftNO: =MATCH('Group List'!$I$11,Shift,0)
GroupFinal: =OFFSET(GroupHeader,ShiftGroupNo,0,ShiftGroupNext-ShiftGroupNo,1)
I'd really appreciate your help.
Thanks in advance.
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.
18 Replies
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
