+ Reply to Thread
Results 1 to 4 of 4

Auto-complete next available number from a column in different sheet, to multiples sheets?

  1. #1
    Registered User
    Join Date
    11-10-2021
    Location
    Newcastle, UK
    MS-Off Ver
    Microsoft Excel for Mac, version 16.54
    Posts
    2

    Question Auto-complete next available number from a column in different sheet, to multiples sheets?

    Hi all,
    I'm brand new here so forgive me if I don't post my question correctly.

    I "think" I'm looking for the impossible, but after using this site to solve numerous issues over the years, just by looking it up, it's time I shouted for help.

    I run a small "business", and I use an Excel Workbook for my invoicing. Let's say, I run "Bob's Cleaning Service" (this is made-up for obvious reasons!).

    I have 3 clients, all of which I send an invoice to after each job. Each client has a different requirement, so they all use different costs and chargeable rates.
    I have set up a workbook that has 4 worksheets. First one is the log - this tells me every invoice I have created for the last year. Then, 3 separate sheets, each an invoice template, pre-filled with each client and their scale of charges.

    The Invoice Log, automatically creates the next available "invoice number" to use, by using a simple formula to lookup the one previous in the column, and add 1 to it, but checking if there is any date entry in the previous column (I like doing it this way, so that I avoid the "Error" or "Value" alerts.

    Hope you're still with me so far, because this is where it gets tricky.

    On each invoice sheet, there is a box (which I have highlighted in yellow) to add the next available invoice number. To do this, I have to look at the "Log" and see what the next available number is. The manually type it into the box on the invoice sheet I intend to use. After I have invoiced a few clients, the sheet saves the last invoice I created, and the invoice number remains from the last time I entered it.

    Due to me being so busy, I have sometimes forgotten to change the invoice number the next time I create an invoice for a client, so they end up with two invoices from me, from different dates, but with the same invoice number. Had I just been using one sheet, I could easily create a simple formula to work out the next one, but as I am using 3 different sheets, It has baffled me.

    I need the highlighted "Invoice Number" box on each worksheet to be able to look at the log and work out what the next available Invoice Number will be.
    Obviously, when copied into all 3 sheets, the same number will temporarily be the same, but as soon as the log is updated, they will all update also (that makes sense in my head..... I hope it came out in words correctly!)

    I have attached a recreated example of what I'm working with - can anyone help?

    Many thanks

    Sparky.


    (PS - I have a basic, self-taught ability to use the simple formulas on Excel, but I am not very technically minded, and still don't understand what an "array" is.....
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Auto-complete next available number from a column in different sheet, to multiples she

    Seems you could use in yellow cells (in all 3 sheets) just a simple formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The only issue is that after filling the data in the invoice and printing it you shall remember to write it's date (alco client and value) in the Log sheet.



    The more advanced solution would be to write a small macro to do both - printout the page and save just-printed invoice data into Log file.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    11-10-2021
    Location
    Newcastle, UK
    MS-Off Ver
    Microsoft Excel for Mac, version 16.54
    Posts
    2

    Re: Auto-complete next available number from a column in different sheet, to multiples she

    Thanks Kaper,
    I've tried writing a small macro, and just get utterly baffled..... guess I'm not clever enough to start with those just yet!

    Sparky :-)

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Auto-complete next available number from a column in different sheet, to multiples she

    I have no chance to test it on Mac, but a set of 2 such macros could do pretty good job:

    In standard module code macro which prints the current sheet, stores data in Log sheet and clears yellow fill from date (see below):
    Please Login or Register  to view this content.


    In ThisWorkbook code the cation to color yellow the date cell if the invoice date is changed. So if you can see it yellow, it has not been pinted and logged yet:
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. [SOLVED] Auto complete number according date
    By andsalex in forum Excel General
    Replies: 6
    Last Post: 07-26-2021, 07:34 AM
  2. [SOLVED] create a new sheets & copy data from sheet to multiples and sum based on month
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-11-2020, 09:56 AM
  3. Copy number criteria from Master to multiples sheets
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-01-2019, 02:16 PM
  4. [SOLVED] Macro Code for Splitting one sheet data into multiples sheets
    By hkbhansali in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-14-2016, 05:06 AM
  5. Help Counting Same cell across multiples sheets with specific sheet name
    By weeblegobble in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-22-2016, 07:42 PM
  6. [SOLVED] Copy Multiples Sheets to One Sheet by Criteria in Column (in All sheets)
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-13-2014, 08:36 PM
  7. Autolist of code in one sheet from multiples sheets in excel
    By NILESH ZALA in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-09-2013, 08:52 AM

Tags for this Thread

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