Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Wink Unanswered: Drop down menu & formula

    Hello,
    I have a drop down menu and would like another cell to automatically insert a price for each item in my drop down menu. Would love anyone who knows to help me.

    Thanks very much. Using Excel for mac

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    You can keep a table on another sheet which has the price for each item. Then, you can have a VLOOKUP formula which returns the price depending on which item was picked from your data validation dropdown list.

  3. #3
    Join Date
    Apr 2012
    Posts
    4

    Wink Drop down menu & formula

    Thank you very much Colin,
    I'm not familiar with that type of formula, any chance you could create so I can see what it looks like?
    Greatly appreciated.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Sure..

    So, I've attached an example. On Sheet1 there's a dropdown list in C2. Pick an item and the corresponding price is returned in cell C3. The formula in C3 is this:

    =IF(C2<>"",VLOOKUP(C2,Table1,2,0),"")

    So, this formula says, "If C2 is blank then return a blank, otherwise look up the exact C2 value in the first column of Table1 and return the corresponding value from the second column of Table1".

    Table1 is on Sheet2.


    The reason I used a table is because you can add more items to it and the data validation dropdown list in C2 will automatically expand with it. Tables were introduced into Excel in version 2007 so you might not be able to use them (depending on which Mac version you have). If you have an earlier version then let me know and I'll give you a different example.

    Bill Jelen has just coordinated a "VLOOKUP week" and collated together lots of articles and videos about VLOOKUP. If you're interested in learning about it then check out the VLOOKUP week blog. You might find Mike Girvin's videos easy to follow (start with video #1).
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2012
    Posts
    4

    Drop down menu & formula

    Thank you so much for doing this Colin, I'm afraid that my version of Excel for mac will not let me open it. It defaults to the Numbers application. Even rich clicking, will not let me choose Excel. Are you able to save it in another format? The file extension I get is XLS

    Thanks gain for your kind help

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Sure, I've converted it to an .xls.

    Code:
    =IF(C2<>"",VLOOKUP(C2,Sheet2!$B$3:$C$12,2,0),"")
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2012
    Posts
    4

    Drop down menu & formula

    Hello Colin,
    Thank you so much, it's greatly appreciated. I will try this on my sheet.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •