+ Reply to Thread
Results 1 to 6 of 6

Drop Down Menu populating Cost box issue

  1. #1
    Registered User
    Join Date
    08-02-2012
    Location
    Missouri
    MS-Off Ver
    Excel 2007
    Posts
    2

    Drop Down Menu populating Cost box issue

    Hey guys first let me say thank you for all the help and guidance you have already given to the lug heads out there like myself. Here is my issue, I am trying to put together a functional invoice. On sheet 1 (called Invoice 1) I have my invoice template. Boxes A17:A39 are drop down menus of services and products. One Sheet 2 (Prices) I have put two lists (they are identical, I just made one a table during this experiment) with the Items listed in boxes C3:25 and prices listed in D3:D25. What I would like to be able to do is when I pick a product/service on the Invoice (Sheet 1) on the drop-down menu (A17:39) then it automatically populates the cost category (C17:39). I have attached a copy of the invoice if anyone would like to see what it is that I am trying to do.
    Thank you in advance for any assistance or help anyone can be. I know it's not a difficult formula but even after all the stuff I have looked up on this website and others I just can't see to wrap my head around it. Thanks again.
    Attached Files Attached Files

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Drop Down Menu populating Cost box issue

    Hi

    Try in C17 and copy down.

    =INDEX(Prices!$G$4:$G$26,MATCH(A17,Prices!$F$4:$F$26,0))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,420

    Re: Drop Down Menu populating Cost box issue

    =VLOOKUP($A17,Prices!$C$3:$D$25, 2,0)


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    08-02-2012
    Location
    Missouri
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Drop Down Menu populating Cost box issue

    Thanks for your help man. That helped a lot, but did bring up one thing I need to add now. I would like to add a "N/A" to the drop down menu (in case I don't use all the spaces on the invoice) and would like for a "0" to appear then on the cost. I have added both the "N/A" and "0" to the item/price list. So now the product list goes from C3:26 and price list D3:26. Right now this is what I get "#N/A" in the price box. Thank you again for the quick help.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,420

    Re: Drop Down Menu populating Cost box issue

    =IFERROR(VLOOKUP($A17,Prices!$C$3:$D$25, 2,0),0)

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Drop Down Menu populating Cost box issue

    You can do this only using this(without NA, in your list)

    =IFERROR(INDEX(Prices!$G$4:$G$26,MATCH(A17,Prices!$F$4:$F$26,0)),0)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1