+ Reply to Thread
Results 1 to 16 of 16

Automatic invoice help

  1. #1
    Registered User
    Join Date
    03-28-2013
    Location
    Wiltshire
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    60

    Automatic invoice help

    Hi all, just looking for some advice in the best route to take for my issue.

    I currently have a weekly page that I use to input how many treatments have taken on any given day. It is a basic input and calculates earnings. I then use the completed information to populate an invoice that I then print for my books but I currently do this manually. I'm sure there must be a way to automate this???

    Currently I have a look up table on my invoice page that populates the info in the invoice once I put treatment number in which saves time but as I have wrote above I'm hoping to skip this process by pressing one button.

    Any help or guidance on this would be greatly appreciated.

    Thanks in advance.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Automatic invoice help

    Please upload the workbook you have and explain witn reference to the information in it how you use it and what results you expect to see.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-28-2013
    Location
    Wiltshire
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    60

    Re: Automatic invoice help

    Sorry for asking but how do I upload?

  4. #4
    Registered User
    Join Date
    03-28-2013
    Location
    Wiltshire
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    60

    Re: Automatic invoice help

    Think I have worked out how to attach.

    Basically there is two sheets one is the weekly sales tracker input where all the information is inputted. I then go to the invoice page and using the lookup table that is on the invoice page, I basically copy whats on the input page into the invoice which I then print for records. Basically is there a simple way to use the information given in the weekly sales tracker to then copy across to my invoice template??

    Thanks in advance.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-28-2013
    Location
    Wiltshire
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    60

    Re: Automatic invoice help

    Any more help on this would be greatly appreciated???

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Automatic invoice help

    You'll need to get rid of the merged cells in cols C:D for this to work.
    In C9
    =IF(ISERROR(INDEX(Wk1!A$4:A$43,SMALL(IF(Wk1!$J$4:$J$43>0,ROW(Wk1!$A$4:$A$43)-3),ROW(1:1)),1)),"",INDEX(Wk1!A$4:A$43,SMALL(IF(Wk1!$J$4:$J$43>0,ROW(Wk1!$A$4:$A$43)-3),ROW(1:1)),1))
    In E9
    =IF(ISERROR(INDEX(K$3:K$42,MATCH(C9,J$3:J$42,0))),"",INDEX(K$3:K$42,MATCH(C9,J$3:J$42,0)))
    In G9
    =IF(ISERROR(SUM(INDEX(Wk1!C$4:I$43,MATCH(C9,Wk1!A$4:A$43,0),0))),"",SUM(INDEX(Wk1!C$4:I$43,MATCH(C9,Wk1!A$4:A$43,0),0)))

    The C9 formula is an array formula & needs to be entered with Ctrl Shift Enter, not just Enter

    See a modified version of your file
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-28-2013
    Location
    Wiltshire
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    60

    Re: Automatic invoice help

    Hi, thanks so much for your help and advice, it is appreciated. Apologies for the late reply, been away.

    I am a very basic user of excel, which you may see from my spreadsheet

    I can see how the invoice page now links to the wk1 data input page but how would it work for subsequent weeks as the invoice page coding looks like it is locked to wk1. I was believing it was possible to have a button to get excel to create an invoice (even if it wasn't my template) produced from the data I have inputted on the data page.

    Sorry for my lack of understanding but any further help would be hugely appreciated. Thanks

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Automatic invoice help

    I've created a template sheet & put this code behind the "Clear Invoice" button.
    Please Login or Register  to view this content.
    This will add a new sheet & name it using cell E2 & will reset the Invoice sheet.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-28-2013
    Location
    Wiltshire
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    60

    Re: Automatic invoice help

    Thanks so much for your help with this. I think I can make this work, this community is awesome. Cheers

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Automatic invoice help

    Glad to help & thanks for the feedback

  11. #11
    Registered User
    Join Date
    03-28-2013
    Location
    Wiltshire
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    60

    Re: Automatic invoice help

    Hi,

    The previous above help was amazing and set me on the right plan but I have come across some new issues which I need some help to try and fix and improve the issues my basic excel brain can't round.

    Problem 1: The last person who replied applied a fix so that once I clicked "Clear Invoice Data" it cleared the sheet down and then created the next week on a new sheet so the example on the attached spreadsheet would be "Wk 2". However when I click the "Clear Invoice Data" button now it comes up with an error and I must have changed or deleted something. I was trying to simplify the spreadsheet from previous versions and was being careful to ensure I didn't make a mistake and was saving it regularly but must have missed this error. So any help would be appreciated.


    Problem 2: When I input information in the "Wk 1" tab the information is then replicated in the invoice page. At the moment the description and quantity comes across but the price column isn't taking the right information and I can't see where the code is wrong for it so again any help would be appreciated.


    Problem 3 (Last problem): On the "Home" sheet in order to simplify I have a basic sales table that adds up the weeks through the year. However to make the overall spreadsheet even easier I would like the sales boxes to find the information automatically from the "wk1" and subsequent sheets. Obviously easy for the week 1 to work now but when the I clear down the invoice and the spreadsheet creates a new sheet, will the next weeks sales boxes pick up the new sheet week after week? Hope this makes sense.

    For note on the attached spreadsheet I hid one of the sheets at the bottom, the hidden sheet is kind of the master input sheet, that it then copies for a new week when I click on the clear invoice tab.


    I would be hugely grateful for any help or advice on any of the above issues and help me to get there with this ready for the new financial year.

    Thanks so much in advance.

    Neil
    Attached Files Attached Files
    Last edited by Lacer75; 03-22-2019 at 04:20 PM.

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Automatic invoice help

    Hi
    Part2, the formula should be
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For parts 1 & 3 use this code
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-28-2013
    Location
    Wiltshire
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    60

    Re: Automatic invoice help

    First of all, thanks so much for your help.

    Problem 1 Perfect
    Problem 2 Perfect
    Problem 3 Perfect

    New problem discovered. When it now creates a new week everything works as it should. However when I input data into the new "Wk2" that information then doesn't go across to the invoice page. The wk1 however works as attended.

    Once again my basic knowledge doesn't stretch me to a solution so any guidance again would be appreciated.

    Thanks again.

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Automatic invoice help

    How about
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    03-28-2013
    Location
    Wiltshire
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    60

    Re: Automatic invoice help

    Looks perfect. Thanks so much, great help!

  16. #16
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Automatic invoice help

    You're welcome & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Automatic invoice generation
    By senthilrajagopi in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-15-2015, 05:27 AM
  2. automatic invoice number
    By hasanbirol in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-31-2012, 12:42 PM
  3. Automatic Invoice Consolidation
    By imranp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-11-2011, 12:39 PM
  4. Trying to make an automatic invoice
    By ace_silver10 in forum Excel General
    Replies: 1
    Last Post: 09-29-2008, 04:56 AM
  5. Automatic Invoice Numbering
    By hockeyman in forum Excel General
    Replies: 7
    Last Post: 08-26-2008, 08:51 AM
  6. SET UP AUTOMATIC INVOICE NUMBERS
    By nothingnice in forum Excel General
    Replies: 2
    Last Post: 01-10-2006, 06:10 PM
  7. [SOLVED] Automatic Invoice Number
    By mb in forum Excel General
    Replies: 1
    Last Post: 01-22-2005, 07:06 PM

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