+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Drop list with invoice auto fill?

  1. #1
    Registered User
    Join Date
    03-13-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    17

    Question Drop list with invoice auto fill?

    Hi everyone

    This is my first post, I can use some help with a product drop list that fills invoice automatically on next sheet
    I've attached a sample work book to give you a better Idea

    Thanks for your help in advanced.
    Last edited by Ray78; 03-21-2012 at 09:07 AM.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Drop list with invoice auto fill?

    you can directly reference the cells, for example

    =Calculator!D3
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Registered User
    Join Date
    03-13-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Drop list with invoice auto fill?

    That's simple enough Thanks... This is related and more complicated for me! In my calculator I have over 16 columns! ordering,cutting and building Items etc. I need to eliminate some of these columns by consolidating.. choosing an item from drop down list in calculator sheet it would look for it in reference sheet and send it to invoice or a report sheet, one formula has to be for text and other numbers
    tried this formula =IF(Calculator!$D$3=Reference!$D$2,Invoice!$B$3,"") in InvoiceB3 but I get nothing in it

    I attached a new sample work book

    Thanks in advanced

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Drop list with invoice auto fill?

    Give this a shot in invoice cell B3:

    =VLOOKUP(Calculator!D3,Reference!B:D,3,0)

  5. #5
    Registered User
    Join Date
    03-13-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Drop list with invoice auto fill?

    It worked great on the first row of the invoice but was getting #N/A on the rows underneath so I went back and used F4 key on the table array fixed that issue now I have to hide the second #N/A on the same rows on invoice, probably from not having any data from the calculator sheet I will try to use the ISNA function so I don't get #N/A when printing out invoices

    Thanks for the help.

  6. #6
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Drop list with invoice auto fill?

    you could also use IFERROR

    =IFERROR(VLOOKUP(Calculator!D3,Reference!B:D,3,0) ,"")

  7. #7
    Registered User
    Join Date
    03-13-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Drop list with invoice auto fill?

    That's a lot shorter than this
    =IF(ISNA(VLOOKUP(Calculator!F2,Reference!$D$2:$F$5,3,0)),"0",VLOOKUP(Calculator!F2,Reference!$D$2:$F$5,3,0))

    This project is starting to come together very nice. sales quote, materials needed with cuts waste, cut sheets, job profit report and Invoicing

+ 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