VLOOKUP, Data Validation

[ashleynjones]

I am trying to put together a spreadsheet that tracks sales for our company. The first column is "CUSTOMER", the company we sold product to. The second column is "SHIP TO", which location/branch we shipped the product to.

Is it possible to type in the customer's name in column A, and have a drop down in column B populate just that customer's locations? We have hundreds of customers, some with multiple ship to locations, and some with only one.

Example: If I type in "Ashley's Grocery", can column B have a drop down (validation table) listing Oregon, Texas, Florida? The customer will not always be "Ashley's Grocery" otherwise a plain ol' validation table would suffice.

Any help on this is very appreciated.

 

[Greg Truby]

That's a common request. Aladin got himself into the Hall of Fame with his answer.

http://www.mrexcel.com/forum/showthread.php?t=51098

 

[ashleynjones]

Thank you, Greg. This was a perfect fix!

 

[snoopyhr]

Here is my 2 cents on this....

The range name "Location" refers to H2 to H10 (where the unique list of location based on Customer is extracted)

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	*	*	*	*	*	*	Helper for DV
2	Customer	Location	*	*	Input Customer	Customer-4	*	Loc-4
3	Customer-7	Loc-3	*	*	Count of Locations	3	*	Loc-2
4	Customer-9	Loc-5	*	*	Locations	Loc-2	*	Loc-1
5	Customer-4	Loc-4	*	*	*	*	*	*
6	Customer-5	Loc-2	*	*	*	*	*	*
7	Customer-5	Loc-2	*	*	*	*	*	*
8	Customer-10	Loc-3	*	*	*	*	*	*
9	Customer-5	Loc-2	*	*	*	*	*	*
10	Customer-4	Loc-2	*	*	*	*	*	*
11	Customer-3	Loc-4	*	*	*	*	*	*
12	Customer-4	Loc-1	*	*	*	*	*	*
13	Customer-3	Loc-1	*	*	*	*	*	*
14	Customer-2	Loc-5	*	*	*	*	*	*
15	Customer-7	Loc-5	*	*	*	*	*	*
16	Customer-6	Loc-4	*	*	*	*	*	*
17	Customer-9	Loc-4	*	*	*	*	*	*
18	Customer-6	Loc-2	*	*	*	*	*	*
19	Customer-3	Loc-2	*	*	*	*	*	*
20	Customer-6	Loc-5	*	*	*	*	*	*
Sheet1

#VALUE!