• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Drop-down list combined with formula based on selection

T

Toby B

New Member
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.
 
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.
 

Attachments

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
 
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? :rolleyes:
 
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
 
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.
 

Attachments

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
 
Good evening Toby B

It is a long standing tradition at Chandoo that when some one uses the words
O wow, that's cool!
Click to expand...
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.....:p
 
@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!
 
You must log in or register to reply here.
Back
Top