Link to home
Start Free TrialLog in
Avatar of flfmmqp
flfmmqp

asked on

Excel 2010 filter by values of another column

I have a worksheet that is provided by another group that I am trying to keep in tact as much as possible.  It has many different field but two of them are Bank and Vault.

Bank                    Vault
PNC                     Omaha
PNC                     SanDiego
BOA                    Pittsburg
BOA                    Nashville
US Bank              Oakland

I have one cell that is grabbing the Bank name via data validation and dropdown list but I need to have another that sees this value and only allows the list to have the valid vault options for the bank that is chosen.  So if they choose PNC then they would only see Omaha and Sandiego in the dropdown list for vault.

How can I do this?  This list has over 770 rows and will change from time to time. I can use VBA to change the data validation if needed.  

Thoughts?
Avatar of oleggold
oleggold
Flag of United States of America image

ASKER CERTIFIED SOLUTION
Avatar of oleggold
oleggold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Patrick Matthews
Please see the attached file:

Q-27927020.xlsx

To get this working:

On the Lookup worksheet, I created a named range, Banks, that listed my banks.

Also on Lookup, I created a list of banks with associated vaults, and created a dynamic named range ("Vaults") to get the vaults for any given bank.  Note that the vaults for any given bank must be on consecutive rows!

I then used data validation on the Data Entry worksheet to first ask for a bank, and then to narrow the list of vaults based on the bank selection.
Avatar of flfmmqp
flfmmqp

ASKER

Thanks this helped the most.