Multiple Drop Down Lists Validation

[marka87uk]

Hi,

I have a list of employees on a sheet name Employees. In column A there are the branch numbers and in column H there are the employee names.

On another sheet I wish to have two columns: in column A I want to have a drop down box which will list each branch number which is in the list of Employees (I can do this already), and in column B I want to have a drop down box which will list all of the employee names in the branch number specified in column A, OR if the branch number drop down box is blank (i.e. branch number is unknown) the drop down box in column B should list ALL employees.

Anyone care to point me in the right direction please?

Many thanks!

 

[VoG]

This may be a start http://www.contextures.com/xlDataVal02.html

 

[marka87uk]

Thanks, but it's not really helping much as I have 1300 possible branches.

I see it's possible to populate the second drop down depending on the value of a fixed cell using OFFSET and INDIRECT, but I need to have the validation extended all the way down the sheet - i.e a branch number can be chosen in any cell in column A and the list in column B of the same row will update accordingly.

Any ideas?

 

[BenMiller]

Thanks, but it's not really helping much as I have 1300 possible branches.

I see it's possible to populate the second drop down depending on the value of a fixed cell using OFFSET and INDIRECT, but I need to have the validation extended all the way down the sheet - i.e a branch number can be chosen in any cell in column A and the list in column B of the same row will update accordingly.

Any ideas?

How could there be a list in one row...? Can you describe the structure a little better?

 

[marka87uk]

Sorry, in any cell in column A there will be a drop down box list with all of the branch numbers.

Once a branch number is selected, the drop down box list in column B should be updated to show only employees in the given branch number of the same row.

Hope that makes sense?

 

[BenMiller]

Sorry, in any cell in column A there will be a drop down box list with all of the branch numbers.

Once a branch number is selected, the drop down box list in column B should be updated to show only employees in the given branch number of the same row.

Hope that makes sense?

No, still not clear. There has to be SOMEWHERE that the second drop-down is pulling its list from: where is that, relative to the source list of the first drop-down?

 

[marka87uk]

I see - it's on another sheet named Employees. The Employees sheet has a list of all employees with their branch number in column A and their name in column H.

 

[BenMiller]

So in column A there are many branch numbers, in nor particular order, with many duplicates. Column H has (unique) employee names. Is that right?

Where are you getting the first drop-down from? Is that a separate location of all the unique values in Employees!A:A?

 

[marka87uk]

Correct on all accounts. The list can be sorted in branch number order though if need be.

 

[BenMiller]

The problem is that Excel does not let you pull validation lists from other sheets, unless it is a named range. Unless you want to name 1,300 different ranges.

If you could put it all on one sheet (even in hidden columns), that would be the easiest. For example (assuming all on one sheet). With your branch numbers in A2:A100 and employee names in H2:H100; and your first drop-down in I1 and second in J1, use this as the formula for the J1 drop-down: =INDIRECT(ADDRESS(MATCH(I1,A2:A100,0)+1,8)&":"&ADDRESS(COUNTIF(A2:A100,B1)+1,8)) ... Note - your data must be sorted on column A (branch numbers).