VLOOKUP or dropdown in the cell depending on selection in another

[Spottydog]

Can anyone suggest a way of doing this. I want to be able to allocate
resources and/or roles.

I have a number of staff members each with a specific role. I have a
dropdown list in the cell so the user can select the relevant staff member.
A VLOOKUP then selects the selected staff members role using named ranges and
a separate spreadsheet. If there is not specific staff member available I
want the user to be able to select a staff member of "To Recruit" and then be
able to select a role type from a dropdown list. My list of staff names
contains around 100 people and there are over 30 possible roles which is why
I have used named ranges
e.g.
Mr A is a BA
Mr B is a Programmer
Mr C is a Tester
The possible available types of roles I have is BA, Programmer, Tester and
Manager.

The dropdown in cell A1 contains Mr A, Mr B, Mr C, & To Recruit

If user selects Mr A in A1 then BA shows in cell B1
If user selects Mr B then Programmer shows in cell B1
If user selects To Recruit in A1 then a dropdown list containing BA,
Programmer, Tester, Manager shows in B1 and the user must select one from it.

So my spreadsheet could show
A B
1 Mr C Tester (auto populated)
2 To Recruit Tester (selected via dropdown)
3 To Recruit BA (selected via dropdown)
4 Mr B Programmer (auto populated)

Thanks

 

[JLatham]

Debra Dalgleish has some good information (and code) on setting up dependents
lists here: http://www.contextures.com/xlDataVal02.html

That may head you toward a solution.

The problem with what you're asking for is that you want the VLOOKUP() to
come up with entries based on the name selected AND you want the option to
use a data validation list. One interferes with the other. You can actually
set up the VLOOKUP() formula and then add data validation to the same cell,
but the pretty much the first time you actually do anything with it all, the
VLOOKUP() gets wiped out and you're left with data validation only - and if
the results of the VLOOKUP() aren't also in your Data Validation list, you
get an 'invalid' error as a no-cost extra.

Unless you find something at Contextures.com, I'm thinking the solution will
involve some VBA coding associated with the worksheet's _SelectionChange
event that will do one of two things when you enter/choose something in the
Column A cell: do the equivalent of a VLOOKUP() and if it finds a match, put
that in the appropriate cell in column B, but if you've entered/chosen To
Recruit (which really should be in your validation list for column A - that
'invalid selection' thing, remember?) then it would set up data validation in
the column B cell so you can pick from that list.