Hi,
I wasn't 100% sure as to how I should label this problem correctly in English so I hope that my headline isn't too far off.
Background:
I'm responsible for our companys MS licenses and to help me keep track of our customers' usage I have a quite extensive workbook, containing one sheet for each customer (built in Excel 2010). Every sheet lists the licenses the corresponding customer is using.
To make the handling more effective I want to create a drop-down list which automatically selects the correct prize for the product chosen.
Let's say that I have a table consisting of 5 columns:
A= Customer name
B= Amount of licenses (e.g. 2)
C= License name (is chosen through a drop-down list)
D= License price (depends on what's being chosen in C)
E= Total cost (B*D)
I have created a drop-down list using the data validation tool (guide here for those who are interested: http://www.databison.com/index.php/d...tion-in-excel/). (The table used by the list is located on a different sheet. Let's call it "OtherSheet".)
Now I want to code it so that when I chose e.g. "ABC 1Proc" from my drop-down list in C2 (cell C2) the price should automatically pop up in D2. I have made this happen with a IF statement referring to the price list on "OtherSheet". However, this causes a problem; let's say that the list consists of 20 different licenses, each having their own price. If I'm going to nestle 20 IF statements in one cell it'll be A LOT of work. Especially if I after 2 months have to add 10 new items.
It would be great if it was possible to maybe tell the drop-down list to fetch a value from location X and place it in location Y whenever I chose something in it.
Does anyone know how to solve this problem?
Is my explanation of what I want to achieve clear enough?
If you have any further questions, please don't hesitate to ask
Best regards
Corm
Bookmarks