Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

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.

share|improve this answer
    
I will try this.. Thanks so much! –  user193411 Jan 28 '13 at 18:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.