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