Data Validation Drop-downs Filled by VLOOKUP of Two-column Table

[changeling jack]

Hello, all:

I am attempting to build an inventory spreadsheet in Excel 2013 on Windows 7. I apologize if this has been covered in detail before, but a search of the boards and the rest of the internet yielded me nothing.

I have generated named tables and ranges on Sheet 2 which fill drop-downs on a table "End of Shift Inventory" on Sheet 1 through data validation. What I am having trouble with is getting the drop-down in Column B to fill dependant on the contents of Column A which have been selected from the DV drop-down.

End of Shift Inventory
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Drink[/TD]
[/TR]
[TR]
[TD]Vault-Tec[/TD]
[TD]Nuka-Cola Quan-tum[/TD]
[/TR]
</tbody>[/TABLE]


The tables on Sheet 2 are "Customers", "Drinks", and "Drinks by Customer". The tables "Customers" and "Drinks" are named ranges which only exist for the purpose of data validation in "Drinks by Customer".
"Customers" (Red Racer, RobCo Industries and Vault-Tec),
"Drinks" (Nuka-Cola, Nuka-Cola Quan-Tum, Sunset Sarsaparilla and Water), and
"Drinks by Customers" which is a two-column named range (rng_Drinks_by_Customer) with an entry for each drink that a customer receives:

Drinks by Customer
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Drink[/TD]
[/TR]
[TR]
[TD]Red Racer[/TD]
[TD]Nuka-Cola[/TD]
[/TR]
[TR]
[TD]RobCo Industries[/TD]
[TD]Nuka-Cola[/TD]
[/TR]
[TR]
[TD]RobCo Industries[/TD]
[TD]Sunset Sarsaparilla[/TD]
[/TR]
[TR]
[TD]Vault-Tec[/TD]
[TD]Nuka-Cola[/TD]
[/TR]
[TR]
[TD]Vault-Tec[/TD]
[TD]Nuka-Cola Quan-Tum[/TD]
[/TR]
[TR]
[TD]Vault-Tec[/TD]
[TD]Water[/TD]
[/TR]
</tbody>[/TABLE]

I wanted to use VLOOKUP to list the drinks which are in the cell next to any instance of the customer name, something along the lines of =VLOOKUP($A$2,rng_Drinks_by_Customer,2,FALSE). This would keep me from having to create new columns every time we add a customer and have endless columns trailing off to the right.

I was told this was possible by a previous employee who somehow managed to make this work on another spreadsheet which has since evaporated so I can't find his method (if this is truly what he ended up doing).

Is this at all possible? Or, do you have any better ideas which don't involve coding in VB?

Thanks much,

Jacob

 

[changeling jack]

bump