Help with drop down box contents to be refered in a calculation.
Posted by Jammy Smith on November 13, 2000 6:02 AM
Ok. Help. I think I know how to create a drop down box. I then can refer it to say 3 cell contents so that the data is selectable using a combo box.
But where that cell where the drop down box is, the figures there, I require in a calculation.
I want to know how to refer the cell contents of a drop down box into a calculation.
Take a simple case. say cell A1 has a drop down box with 1,2 and 3 selectable. I want cell A2 to be =A1*2
I just need to know how to refer. That is all.
Taaa very much!
Posted by JayD on November 13, 2000 6:01 PM
One pretty easy way to do it is as follows:
Assume you want three numbers, 100, 200 and 300, in a drop down list.
Make a table with 1, 2, 3 in cells A1, A2, A3 and 100, 200, 300 in B1, B2, B3.
Create the drop down control, right click on it and select "Format Control..." (or select "Control" from the Format menu). On the "Control" tab will be boxes for Input Range and Cell Link. make the Input Range "B1:B3" and the cell link something like C1. If you select the third item in the list, '3' will appear in cell C1.
Now all you have to do is figure out what the value is of the third item. One way to do it is to put a lookup function somewhere. if you put =VLOOKUP(C1,A1:B3,2) in cell D1, it will put the value of the selection you made in cell D1. "C1" tells it where the index number of the item you selected is located, "A1:B3" tells it where the table is to look in and "2" tells it which column of the table to return to cell D1.
In this example, there is a 3 in C1 (you picked the third item in the list), it will look for a 3 in the first column of the table (A1:B3). When it finds it, it will return whatever is in the second column of the table (because of the '2' at the end of the vlookup function). In this example, 300 is in the second column next to the 3 in the first column so it will make D1 = 300. You can then use D1 in you other equations for the value selected from the drop down list.
It's a lot easier to do than it is to explain. Give it a try.
Jay
