Multiple line invoices

[andy1988]

Hey guys,

I run a small printing company and can't afford to purchase expensive accounts/order management software so I'm making my own in Excel, all went well until I hit a problem and I'm hoping someone can help (and Excel is capable of such a function): to ensure accuracy I need my invoice template to accept multiple line invoices.

Instead of trying to explain my setup I've attached what I've done so far with dummy information for a single 2 line invoice.

As you can see my "invoice" page works nicely simply by entering the invoice number (dummy order 110001). But what I need Excel to do (ideally) is to fill out the invoice lines referring to the "Invoices" page. This would be very easy to do for a single line via VLOOKUP, but I can't work out how to operate this for multiple lines, I don't want each 'product' being listed as a separate invoice number because that makes an accounting nightmare.

My best guess would be a very long string of VLOOKUPs and IF functions to reference the multiple identical invoice numbers, and only fill out the next line of the invoice if there is a duplicate invoice entry on "Invoices" that hasn't already been entered onto the invoice template .

If any of that makes sense I'd really appreciate some help, I can see me copy and pasting the wrong product into the invoice template at some point and causing a problem.

Thanks for any advice,
Andrew

[Cutter]

Try the attached. I have added a helper column to your Invoices sheet and placed formulas in it. The VLOOKUPs on your invoice refer to the results in that helper column.

[andy1988]

THANK YOU!

I'd never have worked that out in a million years. There's only one problem I can't solve now and that's the customer number. Neither the date nor customer number worked on the new file but I corrected the date just by changing the column number, but the same isn't working for customer number (which fills out the address).

I'm using the below, which is technically correct but something is causing it to display #REF!

=VLOOKUP(E12,Invoices!B:B,4)

Should I add something because of the helper column?

Actually this problem appears to have solved itself simply be re-opening the file, so for now it's working fine :D

Thanks again,
Andy

[Cutter]

Glad to hear you got it working.

Don't forget to mark your post as SOLVED. (Click on the FAQ in menu bar for directions)

[mohitsareen]

Hi

We have the same problem and I stumbled upon this thread. Tried the formula in our sheet but it doesn't work.. Attaching the sheet here.. Can you please help ?????

[mohitsareen]

Just a clarification .. the invoice # 38 appears twice in my sheet and that should come correctly in 2 lines :-(

[Fotis1991]

Welcome to the forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.