Can a worksheet column be the source for dependent drop down lists on a different worksheet tab?

[Mr_Phil]

I have an employee worksheet that shows the basics Name, Job, Location, Supervisor etc.
I need to make a counseling form. I would like to make a drop down list of Locations. Then a different dropdown on the other side of the page to select the employee name from a list of employees at that selected location. But, from reading it appears I am going to have to create a table with just those items to serve as data validation sources. Am I reading it right? It just seems to me that what I’ve read is telling me I can’t just use the location column as the first dropdown and then the employees as the second. Maybe I am reading too much into it. Thanks for anyone offering advice.

 

[awoohaw]

If you define your lists as Named Ranges throughout the workbook you can use ='namedrangename' as your data validation source.

Sheet where the names are defined:

Mr Excel Questions 4.xlsm
	A	B	C	D
1	Name	Job	Location	Supervisor
2	John	Clerk	New York	Jane
3	Jane	Manager	Brooklyn	Sally
4	Sally	Executive	Trenton	Sally
5
6	Job	=DVSheet!$B$2:$B$4
7	Location	=DVSheet!$C$2:$C$4
8	Name	=DVSheet!$A$2:$A$4
9	Supervisor	=DVSheet!$D$2:$D$4
DVSheet

Sheet where the names are used for Data Validation:

Mr Excel Questions 4.xlsm
	A	B	C
1
2	DropDown: Name
3
4	Drop Down: Position
5
6	Drop Down: Locaiton:
7
8	Drop Down: Reports To:
9
10
DVMain

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Name
B4	List	=Job
B6	List	=Location
B8	List	=Supervisor

 

[awoohaw]

Here is the name manager (select from formulas menu on ribbon) where you see the 'scope' of the named range:

 

[Mr_Phil]

Thanks. That gives me hope. I will try it tomorrow and make a go of it. I appreciate the time you took to help me out. Thanks again.

 

[Peter_SSs]

I can’t just use the location column as the first dropdown and then the employees as the second.

No, because once you have chosen the location, the number of employees at that location will (probably) be less than the total list of your employees that exist in the original employee column.

You can however do it without named ranges like this. Set up a section in the Employees sheet (or could be anywhere) like columns E:J (or more) below. If you want, those columns could then be hidden.

Mr_Phil.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Job	Location		Location	Loc 1	Loc 2	Loc 3
2	Name 1	Job 1	Loc 1		Names	Name 1	Name 2	Name 7
3	Name 2	Job 2	Loc 2			Name 3	Name 4	Name 8
4	Name 3	Job 3	Loc 1			Name 5	Name 6
5	Name 4	Job 4	Loc 2				Name 9
6	Name 5	Job 2	Loc 1
7	Name 6	Job 3	Loc 2
8	Name 7	Job 1	Loc 3
9	Name 8	Job 2	Loc 3
10	Name 9	Job 3	Loc 2
Employees

Cell Formulas
Range	Formula
F1:H1	F1	=TRANSPOSE(UNIQUE(C2:C10))
F2:F4,I2:J2,H2:H3,G2:G5	F2	=IF(F1="","",FILTER($A2:$A10,$C2:$C10=F1))
Dynamic array formulas.

Then set up your Data Validation like this

Mr_Phil.xlsm
	A	B
1	Location	Name
2	Loc 2
3
4
5
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A2:A5	List	=Employees!$F$1#
B2:B5	List	=INDEX(Employees!F$2:$J$2,MATCH(A2,Employees!$F$1#,0))#

.......

 

[Mr_Phil]

No, because once you have chosen the location, the number of employees at that location will (probably) be less than the total list of your employees that exist in the original employee column.

You can however do it without named ranges like this. Set up a section in the Employees sheet (or could be anywhere) like columns E:J (or more) below. If you want, those columns could then be hidden.

Mr_Phil.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Job	Location		Location	Loc 1	Loc 2	Loc 3
2	Name 1	Job 1	Loc 1		Names	Name 1	Name 2	Name 7
3	Name 2	Job 2	Loc 2			Name 3	Name 4	Name 8
4	Name 3	Job 3	Loc 1			Name 5	Name 6
5	Name 4	Job 4	Loc 2				Name 9
6	Name 5	Job 2	Loc 1
7	Name 6	Job 3	Loc 2
8	Name 7	Job 1	Loc 3
9	Name 8	Job 2	Loc 3
10	Name 9	Job 3	Loc 2
Employees

Cell Formulas
Range	Formula
F1:H1	F1	=TRANSPOSE(UNIQUE(C2:C10))
F2:F4,I2:J2,H2:H3,G2:G5	F2	=IF(F1="","",FILTER($A2:$A10,$C2:$C10=F1))
Dynamic array formulas.

Then set up your Data Validation like this

Mr_Phil.xlsm
	A	B
1	Location	Name
2	Loc 2
3
4
5
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A2:A5	List	=Employees!$F$1#
B2:B5	List	=INDEX(Employees!F$2:$J$2,MATCH(A2,Employees!$F$1#,0))#

View attachment 85217 ....... View attachment 85218

View attachment 85219

Perfect. I don't understand most of it. But it works and I can deconstruct it and learn it. Thank you so much.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.