Data Validation Drop Down List based upon criteria

Queenofmycastle

Board Regular
Joined
Oct 27, 2009
Messages
53
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
NameLocationTelephone #Office
2
Jane DoeSalina
2769880269​
A
3
John DoeSalina
2769880269​
B
4
Joan DoeSalina
2769880269​
C
5
Jane SmithDry Fork
2769889410​
A
6
Jane ThompsonThompson Valley
2769883574​
A
7
John SmithDry Fork
2769889410​
B
8
Joan SmithDry 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
TimeLocationName
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
544
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 27, 2009
Messages
53
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

Well-known Member
Joined
Jan 17, 2014
Messages
544
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,192
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top