View original (French)

Match a price to a product from a dropdown list?

Solved
pcd -  
pilas31 Posted messages 1878 Status Contributor -
Hello,

I'm a bit desperate...
Despite all the help and tutorials I found online, I'm trying to create an Excel file under 2007.

The question is quite simple: How do I match a price to each of my products in my drop-down list?

Basically, I have a drop-down list with 89 products and I would like the price to automatically appear in another column each time I select a product.

By the way, I'd also like to ask how I can remove a drop-down list function from a column? I had created a drop-down list for the product prices, but I no longer need it since I want the prices to show directly based on the selected product. I can't seem to remove the little arrow of the drop-down list!

Thank you for your help.

PCD

2 answers

  1.
    pilas31 Posted messages 1878 Status Contributor 648
     
    Hello,

    A little example:

    Let's imagine that the price list is in the table from A2:B21 with the product name in column A and the product price next to it in column B.
    The drop-down list with the product choice in G1
    Here is the formula that gives the price based on the product:

    =VLOOKUP(G1,A2:B21,2,FALSE)

    For the second topic.
    Designate the cell with the drop-down list to remove
    Go to the "Data" tab in the top menu
    Click on data validation
    In the small window that appears, click on the button at the bottom left "Clear All"

    See you later

    Best regards,
    2
    1. pcd
       
      Hello and thank you!

      I will try right away!
      0
    2. pcd
       
      Unfortunately, it doesn't work!
      Maybe if I give more information, it will change the formula:

      My dropdown list is on Sheet 1
      - Product cell: A21-B21-C21-D21-E21: that's where my dropdown list is located (the products are in merged cells)
      - Cell where I want the price displayed: G21

      My data tables are on Sheet 2:
      - Cells A1 to A89 contain my product list
      - Cells B1 to B89 contain my corresponding price list

      Is it still the same formula since my merged product cell doesn't allow me to just put G1 for example?
      0
    3. pilas31 Posted messages 1878 Status Contributor 648
       
      The problem comes from the fact that the formula needs to be modified to go to sheet 2, I think like this:
      in Sheet 1 cell G21

      =VLOOKUP(A21,'Sheet 2'!A1:B89,2,FALSE)

      Normally, the reference to a merged cell is the first cell, so here A21.
      1
  2. pcd
     
    I did exactly that, the good news is that the formula seems to work because I have a price that displays well, but the problem is that the price is displaying as "€0!!!!
    1
    1. pilas31 Posted messages 1878 Status Contributor 648
       
      Well, it's impossible to understand why, I think the only solution is to give us the example file (if possible) by removing the confidential data, posting it on www.cjoint.com, and providing us with the download link in a post.
      0
    2. pcd
       
      Here is the link to the file! http://cjoint.com/?3BvpVEIGEhE
      Thank you for your help!!!
      0
    3. pcd
       
      I think I succeeded; my data range was wrong!!!
      Thank you for everything anyway.
      0
    4. pilas31 Posted messages 1878 Status Contributor 648
       
      It's not a big deal...
      In the cell formula G21, replace Sheet1 with Sheet2.
      0