4 Levels of Dependent Drop Down Boxes

[adray13]

Hi,

I have a list of Business codes that have four levels (Business, Division, Department, and Group). So, column A is a list of Businesses, column B is a list of Divisions under each Business, column C is a list of Departments under each Division, and column E is a list of Groups under each Division. When someone selects a Business from the drop down I would like the next drop down to contain only the Divisions under the Business they choose and so on. I have attached a spreadsheet with a sample of the data.

Any help would be much appreciated.

Thank you!

[FDibbins]

You will need to create a list of each group and sub-group, and give each 1 a range name. (I will leave that to you). I have attached a sample WB showing 3 levels, and how that works. You would need to add your 4th level.

[ChemistB]

This could be done with a lot of dynamic named ranges and Arrayed formulas so that you don't need to create 4 layers of lists but I will work on it tomorrow.

[ChemistB]

Okay, you will need to alphabetize your list for this to work.
Select A1:D26 > Custom Sort
by Business, then Add a level, by Division, Add a level, by Department

In F2 copied down, this ARRAYED Formula

=IFERROR(INDEX($A$2:$A$26,SMALL(IF(COUNTIF($F$1:F1,$A$2:$A$26)=0, ROW($A$2:$A$26)-1), ROWS($A$1:$A1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Then in Name Manager (Formula tab),

DivA =INDEX(Sheet1!$B$2:$B$26, MATCH(Test!$A$2, Sheet1!$A$2:$A$26,0)): INDEX(Sheet1!$B$2:$B$26, MATCH(Test!$A$2, Sheet1!$A$2:$A$26))=INDEX(Sheet1!$B$2:$B$26, MATCH(Test!$A$2, Sheet1!$A$2:$A$26,0)): INDEX(Sheet1!$B$2:$B$26, MATCH(Test!$A$2, Sheet1!$A$2:$A$26)

This formula pulls the range of cells relevant to the business chosen in sheet "Test"

Then in G2 copied down, this Arrayed formula

=IFERROR(INDEX(DivA,SMALL(IF(COUNTIF($G$1:G1,DivA)=0, ROW(DivA)-INDEX(ROW(DivA),1)+1), 1)),"")

Then back in Name Manager,

DepartA =INDEX(Sheet1!$C$2:$C$26, MATCH(Test!$A$4, DivA,0)+INDEX(ROW(DivA),1)-2):INDEX(Sheet1!$C$2:$C$26, MATCH(Test!$A$4, DivA)+INDEX(ROW(DivA),1)-2)

this pulls the range dependent on what Division was picked in sheet "Test"

Then H2 copied down, this ARRAYED Formula

=IFERROR(INDEX(DepartA,SMALL(IF(COUNTIF($H$1:H1,DepartA)=0, ROW(DepartA)-INDEX(ROW(DepartA),1)+1), 1)),"")

Then in Name Manager

Group =INDEX(Sheet1!$D$2:$D$26, MATCH(Test!$A$6, DepartA,0)+INDEX(ROW(DepartA),1)-2):INDEX(Sheet1!$D$2:$D$26, MATCH(Test!$A$6, DepartA)+INDEX(ROW(DepartA),1)-2)

Then in I2 copied down, this (not arrayed) formula

=IFERROR(INDEX(Group,ROWS($A$1:$A1)),"")

Lastly, for purposes of using data validation and dropdown menus, in the name manager

Business = Sheet1!$F$2:INDEX(Sheet1!$F$2:$F$21, SUMPRODUCT(--(LEN(Sheet1!$F$2:$F$21)>0)))
Division =Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$21, SUMPRODUCT(--(LEN(Sheet1!$G$2:$G$21)>0)))
Department =Sheet1!$H$2:INDEX(Sheet1!$H$2:$H$21, SUMPRODUCT(--(LEN(Sheet1!$H$2:$H$21)>0)))

See attachment

[adray13]

I have attached my actual data I am using to show you what I have tried to re-create so far. I didn't want to overwhelm anyone seeing almost 3000 rows of data. I am assuming I need to alter the formula in column F to incorporate all 2652 rows which I did. I also changed the DivA and DepartA in the name manager to incorporate 2652 rows. Something weird is happening with the Department drop down though. It repeats the same Department several times. Is there something I am missing that I need to also edit for this to work?

I found the Name Manager by the way!

Thank you for any help you can provide.

[ChemistB]

Okay, you hadn't updated the rows in GROUP to 2562 in Name Manager

Edit. Also noted you did not continue the formula down far enough in F to include all busineesses. I suggest F,G,H and I go down to row 30 or so.

[ChemistB]

Updated sheet with the expanded ranges

[adray13]

Thank you all! ChemistB, I am trying to recreate what you have done so that I can learn how you did it. This may be a silly question, but I can't figure out where to put the formula in the Name Manager you reference. Can you point me in the right direction? Much appreciated.

[ChemistB]

Okay, so you found Name Manager?

Then click on "New", then in Name box type DivA
scope = workbook
Refers to
=INDEX(Sheet1!$B$2:$B$26, MATCH(Test!$A$2, Sheet1!$A$2:$A$26,0)): INDEX(Sheet1!$B$2:$B$26, MATCH(Test!$A$2, Sheet1!$A$2:$A$26))=INDEX(Sheet1!$B$2:$B$26, MATCH(Test!$A$2, Sheet1!$A$2:$A$26,0)): INDEX(Sheet1!$B$2:$B$26, MATCH(Test!$A$2, Sheet1!$A$2:$A$26)

Then "OK"

Does that answer your question.

[ChemistB]

Yep, my fault. I gave you the wrong formula for Column H

=IFERROR(INDEX(DepartA,SMALL(IF(COUNTIF($H$1:H1,DepartA)=0, ROW(DepartA)-INDEX(ROW(DepartA),1)+1), ROWS($A$1:$A1))),"")

That should fix everything. Nice job setting it up. Any other questions?

EDIT: I see we are having a problem with "GROUP" also. Looking into it.

[adray13]

Woohoo! It works!!! Thank you so much.