Greetings!
(I have edited this post to in an attempt to explain things better.)
Say in B1 I have a drop-down list with the following as it's data source:
B2=Apple
B3=Pear
B4=Orange
B5=Mango
A1 has the formula "=25*B1", with the intent that depending on what the user chooses in B1, A1 will change it's value.
Obviously the formula in A1 won't work, since none of the choices available to the user in B1 are actual numerical values.
Now say in the C column I have the values:
C2=9.5
C3=1.3
C4=8.4
C5=12.4
Is there a way to make it so when the user chooses a B value from the drop-down list, A1 will instead use whatever is in the B1 cell to refer to a corresponding C value which can then be multiplied in the formula?
For example the User chooses "Orange" in the B1 drop-down list. A1 will utilize "Orange" (B4) to refer to the value in (C4) which is 8.4. Now the formula will work, (=25*8.4). Is there a function or a method to accomplish this?
I use Excel 2007.
Thank you very much!
Last edited by pandaman; 12-15-2010 at 07:32 PM.
A MATCH() can accomplish this. If your list of fruits is B2:B10, then let your B1 drop down display those items, then the A2 formula would be:
=9*MATCH($B$1, $B$2:$B$10, 0)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks for your suggestion JB, but unfortunately that's not quite what I was looking for.
Although the MATCH function does work for the example I gave, it was a bit of a coincidence. I didn't mean for my B Column values to start from 1 and go in sequential order.
Allow me to revise the scenario.
Say in B1 I have a drop-down list with the following as it's data source:
B2=Apple
B3=Pear
B4=Orange
B5=Mango
A1 has the formula "=25*B1", with the intent that depending on what the user chooses in B1, A1 will change it's value.
Obviously the formula in A1 won't work, since none of the choices available to the user in B1 are actual numerical values.
Now say in the C column I have the values:
C2=9.5
C3=1.3
C4=8.4
C5=12.4
Is there a way to make it so when the user chooses a B value from the drop-down list, A1 will instead use whatever is in the B1 cell to refer to a corresponding C value which can then be multiplied in the formula?
For example the User chooses "Orange" in the B1 drop-down list. A1 will utilize "Orange" (B4) to refer to the value in (C4) which is 8.4. Now the formula will work, =25*8.4
At first I thought LOOKUP would be the right answer, but I can't seem to get it to work. Though it's possible I'm doing it wrong.
VLookup() is the right answer.
=9*VLOOKUP($B$1, $B$2:$C$5, 2, 0)
LOOKUP() would have worked if your fruit list was ordered.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
That's exactly what I was looking for! Thank you !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks