0

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.

Improve this question
2
  • Does this selection only happen once in the sheet, or is it on every row that they select the division and then the manager? Commented Aug 21, 2014 at 9:25
  • The division is only selected once. It is always at cell $D$4. Commented Aug 21, 2014 at 9:30

1 Answer 1

Reset to default
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:enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

The OP mentioned dynamic data for the managers, so I bet your answer would be most helpful if you can make it dynamic as well. Using OFFSET perhaps.
Thanks. I am trying to work out how to adjust that solution to my problem.
Hi @StorymasterQ, it is dynamic - if you change the selected Division in D4, the drop-down changes
The range B2:B6 can go over the end of the list e.g. B2:B999... So if you add a manager, it will automatically be included.
@GustavoBaiocchiCosta let me know if you need any help! And good luck!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.