Dec 30 2021 08:27 PM
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.
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.
Dec 31 2021 06:03 AM
@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.
Jan 02 2022 05:03 AM
SolutionHi @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.
Jan 19 2022 07:39 PM
Jan 19 2022 07:41 PM
Jan 19 2022 10:17 PM
Oct 27 2022 09:27 AM
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!
Oct 27 2022 11:47 AM
Oct 27 2022 12:12 PM
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
Shift | Group | Name |
Text | Video 1 | Employee 1 |
8:00 | Video 1 | Employee 2 |
8:00 | Video 2 | Employee 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
Shift | Group | Name |
#VALUE! | #VALUE! | #VALUE! |
Nov 10 2022 10:53 AM
@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.
Dec 07 2022 06:54 AM
Feb 02 2023 12:23 PM
Hi @svgvervit It is possible eg as attached.
The formula in column L handles the different entered possibilities to present the corresponding choices and row 6 is like an if-statements 'else'.
Choice1 is a defined name for the whole column G, Choice2=$H:$H and Choice3=$i:$i
=TRANSPOSE(IFS(
@Choice1&@Choice2&@Choice3=""; UNIQUE(Table1[Cat 1]);
@Choice2&@Choice3=""; UNIQUE(FILTER(Table1[Cat 2];Table1[Cat 1]=@Choice1));
@Choice1&@Choice3=""; UNIQUE(FILTER(Table1[Cat 3];(Table1[Cat 2]=@Choice2)));
@Choice3=""; UNIQUE(FILTER(Table1[Cat 3];(Table1[Cat 1]=@Choice1)*(Table1[Cat 2]=@Choice2)));
1; "#N/A"
))
The data validation in cell i24 first tests if the cell itself is not empty and in that case only have its own value as allowed choice.
If it is not empty, it will allow the values in column L including its spilled results (the hashmark in L24# addresses also the spilled cells).
=IF(I24<>"";I24;$L24#)
NB - my local settings use semicolon as list separator - not comma.
Feb 03 2023 07:27 AM
@bosinander It worked! Thank you so much!
Apr 26 2023 11:26 AM
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
Apr 27 2023 12:32 AM
Hi @Alex_Q
If you want them as rows instead of as columns, the logic has to be transformed as attached.
May 20 2023 05:17 AM
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.
Jan 02 2022 05:03 AM
SolutionHi @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.