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

Sample Dropdown.xlsx157 KB

excel

office 365

bosinander
Jan 02, 2022
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.
Dependent Dropdown List.xlsx20 KB

bosinander

Iron Contributor

Feb 02, 2023

Hi svgvervit It is possible eg as attached.

Since g9:i9 spills downwards, they would interfere with the next rows possible choices.
Therefore, the unique choices are transposed to be presented horizontally.
The row can without more complexity only hold one set of allowed choices.
Column L thus holds the choices for the next empty cell.
Already made choices cannot have any other choices than what is already chosen.
This also makes sense logically - it should probably not be allowed to change a hierachically higher choice when there are still made lower ones).
As soon as one choice is made, this setup prepares for the next empty cells choices.
To change the last choice, the cell first has to be cleared.

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