Drop-down list combined with formula based on selection

[Toby B]

I'm trying to write a formula where the the resulting number is based on the item selected in the drop-down list.
Here is the set-up.

• The drop-down list has 4 items with different names.
• Each item is a different price.
• Depending on the selection I need the final number to be the result of the item multiplied by the quantity of item purchased.

What I've set up so far:

• A list of the items off to the side, in another cell the price of the item, and another cell with the quantity.
• Set up the drop-down list.
• A cell where the formula and result will be.
• Tried to get a formula to work.

Hope that makes sense.
It should be easy, but it's just eluding me.

 

[bobhc]

Good day Toby
do not see why it is not working, can you post a sample of your worksheet

 

[Toby B]

I don't have excel at home, so I did a simple re-creation of what I am trying to do in Calc.
See the attached PDF.
I know enough about excel to get myself in trouble, but I have a hard time understanding how to write formulas correctly.

 

[NARAYANK991]

Hi Toby ,

Is your formula in D6 something like the following ?

=$B6*VLOOKUP($C6,$F$7:$G$10,2,FALSE)

If so , does it not work ?

Narayan

 

[Toby B]

Narayan, that worked wonderfully! Thanks!
I was trying an IF function.
Now, I'll need to study that formula, so I can wrap my brain around why it works...I think the only thing I don't get yet is the number 2. Does it mean column 2?
Can you tell I'm a newbee?

 

[NARAYANK991]

Hi Toby ,

You are right ; the range $F$7:$G$10 is 2 columns wide , and the 2 in the VLOOKUP's third parameter refers to the second column i.e. column G.

All of us were newbies at some stage !

Narayan

 

[Toby B]

Thanks so much for your help! This just opened up a whole new world, and made my life easier.
Toby

 

[bobhc]

Good day Toby B

If you use tables then new data enters can be made with out having to edit the data validation.
The upload has Narayan's formula but I have changed it as I changed the data order, no other reason than it makes more sense to me.
And I have as said turned the data in both parts to tables.

 

[Toby B]

Bobhc,
O wow, that's cool! I was like a kid with a new toy, now I have a newer toy. :D
For the application I am asking about, it's not about food or anything like that, but I will only have one choice selected in a drop-down. I can see where I could use this method elsewhere.
Thanks you guys! You're the best!
Toby

 

[bobhc]

Good evening Toby B

It is a long standing tradition at Chandoo that when some one uses the words

O wow, that's cool!

they follow it up by sending gifts to all those who helped in getting you your answer.....Not idea what the others want but I will take money, whiskey, carlsberg or any thing with value.....

 

[Toby B]

LOL!!! The checks in the mail.:D

 

[SirJB7]

@b(ut)ob(ut)hc
Hi!
Any doubt about that the correct order is money (to buy Carlsberg), Carslberg, gold (it shines so nice!)?
Regards!