Invoicing master sheet from others

bobbybrown

Board Regular
Joined
Apr 17, 2015
Messages
121
Hi,
I'm trying to get my accounts in order here and as such I have a folder full of invoices, all exactly the same layout as I use a template file that I made to create each invoice.

What I am trying to figure out is:
Is there a way that I can create a master sheet in excel with the field name across the top such as invoice number, date, name, product, price, transaction id then have the master sheet scan my folder and pick the information out of every invoice sheet automatically using a macro and populate the master sheet as a big list of sales?

Some people have more than 1 product so not sure how this could be handled

I could then autosum the total price so I can keep a running total of invoice totals.

If there was a way to re-run this periodically but have the macro only read new files that would be perfect too but I'm not sure if something like this could be done.

I guess first I am looking to see if this is possible and if so the best way to go about it.

Many thanks to you for reading my post!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sounds like a lot of work, but in theory:

Are all invoices located in one folder or within subfolders of one main folder?

How do you identify an invoice sheet? Does the sheet name have a standard format/structure?

More than 1 product? I assume this is on the one invoice? You could have the code look at the invoice and then each product on the invoice and give the same invoice number for each product?

Running periodically? That can mean a few different things. Do you want the workbook to run each time you log in to your computer? On a set date? - this would probably need to be done on a server side system. I can't do this myself, but put a note in my diary to run certain workbooks if needed.
If you need it to look at only new files, is the invoice number part of the document name? Could your main workbook store the last invoice number it looked at and not extract anything lower (possibly also retaining the last time run and comparing this to the modified date of any invoices so only new ones get added)

It might be possible, but much of how it would be done would depend on how various parts are structured.
 
Last edited:
Upvote 0
Hi,
Thanks for your reply.
They are all in one folder on my desktop, named 01 name, 02 name and so on. The file name is the invoice number and customer name. "01 John smith" as an example.

All the invoices are laid out exactly the same in the same structure, each invoice holds the invoice number, customer name, address, products they have purchased, price and PayPal transaction ID number.

Where they have more than 1 product at the same time it is on the same invoice however this isn't a frequent occurrence as most people have just 1 product.

By running periodically I was thinking maybe each month, I could create the invoices as normal each month and run the code that would scan and pick up any new invoices and add them to the master sheet.

I could manually run the code that's no issue, it's just making sure it doesn't duplicate any records.
 
Upvote 0
So if we were to do this is pseudo code - just to make sure everything is covered:

for each excel file in desktopFolder
If LastInvNo (from master workbook) < mid(filename,1,instr(1, filename, " ") -1) then
[this looks for the first space in the file name and compares the number before this with the last invoice saved to the master workbook]
open invoice workbook
extract data & update LastInvNo
close invoice workbook
end if
next excel file

Does this sound like what you are trying to achieve?
Are the invoice number sequential?
01 John Smith
02 John Smith
03 Damien Gray
04 John Smith - or would this be 03 as in the 3rd invoice for that customer?
 
Upvote 0
That sounds like what I'm trying to do. The numbers are sequential so if john smith had invoice 02 then ordered 6 weeks later he would also another invoice with a higher number, whatever we were at when he ordered again, for example 94 John smith if that makes sense?
 
Upvote 0
Okay, so the sequencing is progressive and not based on customer name.
Can you provide a dummy example of an invoice with more than one product to show the layout?
Just make up the data, but show the layout - you should be able to create a table using the tools from the "Go Advanced" section.

Also, what would be the required layout of the Master workbook?
 
Last edited:
Upvote 0
That's no problem. Where would you like me to send it?

Just seen about making a table, I'll give that a go shortly and will also post an explanation of what info is in what cells.
 
Last edited:
Upvote 0
Hi,
It won't let me create the layout the same as on the invoice, however,
Customer name is in cell B8
Invoice Number = C16
Order date = C17
Product Quantity = B21
Product = C21 (in the case of multiple products this would be C21, then C22, C23 and so on)
Unit Price = I21
Total price= J47
Transaction ID = D28

I hope you can visualise the layout from that...
 
Upvote 0
I won't be able to look at this until tomorrow, but what happens if the number of products goes beyond C47?
Does the total price just get moved down?

And what is the data that will be stored in the Master workbook?
invoice number, date, name, product, price, transaction id, Invoice Total ? Product Quantity?

 
Upvote 0
J47 is the total price of all the products, the sub-total I guess.

The products sold won't ever go as far as C47.

You are correct on what would be ideal for the master workbook. Thankyou for helping with this!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top