SOLVED

Dependent Dropdown List With Pivot Table

Contributor

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.

 

Link 

 

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.

12 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.

best response confirmed by kheldar (Contributor)
Solution

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.

bosinander_1-1641127950459.png

 

=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.

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

@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!

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..?

@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!

 

 

 

@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.

bosinander_1-1668106183700.png

 

 

Thank you so much!
You're welcome
@bosinander I am working on a similar scenario. I was wondering if it is also possible to pull the lists in the cells G2:I2 to the cells downwards (3-4-..), and that the formula's in de cells G9:I9 automatically adjust along with them to the new row? So every row gets a unique list, dependant on what is chosen in the first column (cat1)? If I pull the lists downwards right now, the formula keeps comparing with the cells in the second row.
Thanks in advance!