Invoicing master sheet from others

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 46

Thread: Invoicing master sheet from others

  1. #1
    Board Regular
    Join Date
    Apr 2015
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Invoicing master sheet from others

     
    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!

  2. #2
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    950
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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 by George J; Jun 11th, 2017 at 11:04 AM.
    George J

  3. #3
    Board Regular
    Join Date
    Apr 2015
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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.

  4. #4
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    950
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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?
    George J

  5. #5
    Board Regular
    Join Date
    Apr 2015
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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?

  6. #6
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    950
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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 by George J; Jun 11th, 2017 at 12:06 PM. Reason: forgot something
    George J

  7. #7
    Board Regular
    Join Date
    Apr 2015
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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 by bobbybrown; Jun 11th, 2017 at 12:30 PM.

  8. #8
    Board Regular
    Join Date
    Apr 2015
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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...

  9. #9
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    950
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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?

    George J

  10. #10
    Board Regular
    Join Date
    Apr 2015
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

      
    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 by bobbybrown; Jun 11th, 2017 at 06:49 PM.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com