# How can I create a drop down list which can be filtered by another cell's value?

I have an excel sheet that our salesman need to use for order requests. I have created a drop down for descriptions and to auto-populate the daily weekly and monthly rental rates.

There are multiple branches that all use same equipment but have different rental rates. How can I achieve the proper rates when a choice is made in the drop down?

Worksheet A has a cell that allows the user to select the Branch.

Worksheet B contains rows of equipment choices:

Cat Class Item Description Branch Daily Price Weekly Price Monthly Price

I've been able to work with my IT guy a little, and he was able to get the rates to pull in using the formula , however he said he can only get this to work by saving a new file order sheet/excel file for each branch?

=IF(\$L18 <>"",INDEX(Sheet1!\$D\$2:Sheet1!\$D\$200,MATCH(\$L18,Sheet1!\$H\$2:Sheet1!\$H\$200,0)),"")

Any help in getting this "automated" would help... Hopefully this explains things further?

-
Please edit your question and tell us what you have tried tried, how the data is arranged, etc? – CharlieRB Jan 28 '13 at 13:29

You probably need the INDIRECT function to solve this.

here's what I did:

create a range "BranchList" containing "East" and "West".

Create a range for item numbers.

Create a range for "East" and for "West". These ranges contain a list of item numbers and their corresponding rates for the branch. Their range names must correspond to the "BranchList" names.

In cell A2, create the drop-down for the branch choice.

In cell A6, create the drop-down for the item number choice.

And then, the kicker: in cell B6, put the following formula:

``````=VLOOKUP(A6,INDIRECT(A2),2)
``````

The `INDIRECT` command looks up cell A2 and treats it as a reference. So when I select the East branch in cell A2, my command becomes `VLOOKUP(A6, EAST, 2)` and so it looks in the EAST range and retrieves the correct rates for the East branch. If I select the West branch in cell A2, my lookup turns into `VLOOKUP(A6, WEST, 2)` and therefore retrieves the correct rates for the West branch.

-
I will try this.. Thanks so much! – user193411 Jan 28 '13 at 18:37