Data Validation Drop Down List based upon criteria

[Queenofmycastle]

I asked this question earlier and all my tabbed information just cluttered up, I figured out how to show spreadsheets and decided to try again with a new post. I hope that's OK. I feel like the formula needed to make this work has got to be simpler than I am making it.

Thanks so much!


Excel 2007

Row\Col	A​	B​	C​	D​
1​	Name	Location	Telephone #	Office
2​	Jane Doe	Salina	2769880269​	A
3​	John Doe	Salina	2769880269​	B
4​	Joan Doe	Salina	2769880269​	C
5​	Jane Smith	Dry Fork	2769889410​	A
6​	Jane Thompson	Thompson Valley	2769883574​	A
7​	John Smith	Dry Fork	2769889410​	B
8​	Joan Smith	Dry Fork	2769889410​	C

Sheet: Table1

My goal is to have a data validation drop down list for Column C on Table 2 that limits the choices to only the names of people whose location matches the same row in Column B.

I hope this is a better explanation!

Excel 2007

Row\Col	A​	B​	C​
1​	Time	Location	Name
2​	8:00 AM​	Thompson Valley
3​	9:00 AM​	Salina
4​	10:00 AM​	Dry Fork
5​	11:00 AM​	Salina
6​	12:00 PM​	Dry Fork
7​	1:00 PM​	Dry Fork
8​	2:00 PM​	Thompson Valley
9​	3:00 PM​	Thompson Valley
10​	4:00 PM​	Salina
11​	5:00 PM​	Salina

Sheet: Table2

 

[C Moore]

If Table 1 is sorted by location, you could use the name manager with a dynamic range. With C2 selected on table 2, open name manager, location_names=OFFSET(Table1!$B$1,match(Table2!$B2,Table1!$B:$B,0),-1,countif(Table1!$B:$B,Table2!$B2)). Data calidation then is a list =location_names.

 

[Queenofmycastle]

I definitely think that is on the right track and I appreciate your advice. I copied and pasted the formula directly in and the results with Data Validation for Dry Fork are John Smith, Joan Smith, and Jane Doe. Salina is John Doe, Joan Doe, and Jane Thompson. Thompson Valley Yields no results. Can you advise?

 

[C Moore]

Paste the formula in the cell next to Thompson Valley. It will give an error, but you should be able to step through it with evaluate formula to see where the mistake is occurring.