Excel Data Validation as input to another Data Val

Question

Assuming I have a worksheet with the following information:

Manager    Division
Gustavo        1
John           2
Jack           2
Paul           1
Simona         2

I have a data validation list that allows the user to select a division. If the user selects 1, then in another data validation list I want to list Gustavo and Paul. IF the user selects 2, then in another data validation list I want to list John, Jack and Simona. Moreover, the data might scale. What I mean is: maybe below Simona another user can be added, let's say: Berry 1. Then if the user selects 1, then Gustavo, Paul and Berry will be the options for the other data validation list.

I have already implemented the first validation list. The problem I am having is with the second part of the problem. Most solutions out there uses name managers. This is a problem for me because the way my data is laid out and because I need to keep constantly updating name managers. I would like to make it more dynamic, when a user adds or remove data, it always shows the current list for that division. I will have a third validation list afterwards, however, if I can learn how to do this one, then I should be able to solve the rest of the problem.

Answer 1

OK, so you can do this in a couple of steps with a working range to hold the validation list:

1. Somewhere in your workbook, you create an array formula in multiple cells
   • select cells e.g. F2:F6 (I'm using 5 possible Managers per division in the example, but you can change that)
   • With them all selected, enter the ARRAY formula (i.e. use Ctrl+Shift+Enter to enter it) =SMALL(IF($B$2:$B$6=$D$4,ROW($B$2:$B$6),""),ROW(INDIRECT("1:5"))) - the managers' divisions are in B2:B6 - the selected one is in D4... the 1:5 is a effectively a counter, going up to our 5 possible managers.
   • This should give you a list of row numbers where the Division is the same as selected and then some #NUM! errors
2. In the next column (G2:G6) enter the (normal) formula =IFERROR(INDIRECT("A"&F2),""). This will give you a list of the names of Managers in the selected division.
3. In the cell you want the selection, use the source =OFFSET($G$2,0,0,COUNT($F$2:$F$6)) - this references the list of names, but gets rid of the blanks at the bottom

Hope this makes sense! Here is a picture of the layout: