Pammi
I think you need to add an If statement
=IF(E5="",your lookup,E5) or =IF(E5<"",E5,your lookup)
different versions of the same equation.
Lou

"JLatham" wrote:

Bob,
Yes your solution is possible. If PammiJ uses the version that does not
have code associated at the Worksheet event level. There's one version that
does use code in the sheet's _Activate() event.

I'm kind of confused at this point - First, I'm not sure which version she
is speaking about, or how she's trying to use it/them. NONE of the 3 options
I gave her will automatically renumber a sheet once an invoice number has
been placed in the cell where it is to appear on an invoice sheet. I'll have
to test to see if any of them would do so if the sheet were separated out and
moved into another book or something like that. I have not received email
today with reply to my request for her to explain her process for creating
and saving invoices. I'm actually not even sure she's using any of them from
a .xlt file vs just reusing a .xls file.

As a matter of fact, two of the options actually initially required that she
click a button to get a new invoice number, the other (3rd) had a sheet to
use a template for new invoice sheets with blank cell where invoice number is
to go, and when that sheet was copied to new sheet, then the invoice number
was placed into the new sheet with the source sheet keeping a blank cell
there for future reference: This 3rd version that works by assigning invoice
number to newly created sheets from the one has this code at the Worksheet
level:
Private Sub Worksheet_Activate()
If ActiveSheet.Name = "Invoice" Then
Exit Sub ' do not put number on basic template sheet
End If
If IsEmpty(Range(InvoiceNumberLocation)) Then
Range(InvoiceNumberLocation) = IncrementInvoiceNumber
End If
End Sub

Where "Invoice" is the sheet name of the 'template' sheet;
InvoiceNumberLocation is the cell where the invoice number is to be placed;
and IncrementInvoiceNumber is actually a function that determines what the
next number is to be.

So, Rule #1 - don't put number on the template invoice sheet, and
Rule #2 - don't overwrite an existing invoice number already on a sheet.

"Bob Phillips" wrote:

You obviously know a lot more about the situation than me, but whichever
course they take, my advice still holds good does it not? The post was
asking how not to take the code with the template.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Bob - to explain. First must determine which of 3 possible workbooks
she's
talking about. One was developed in a long thread elsewhere and uses an
external .txt file to contain the last invoice number used. Two others
use a
Name to keep the number internal to the file. Earlier versions had the
code
updating the invoice number entry every time the file was opened - that
was
from original code obtained from another site.
I've modified the code in that one, and set up code in the two other
versions to never insert an invoice number into a cell with an invoice
number
already in it.

Consider the version with the external .txt file tracking last used
invoice
number: you open the template file, it pops the next invoice number into
the
proper cell. You open that same file again, the code will not do that
because it will see there is already an invoice number in it.

In the case of the one with the invoice number kept internally, there is a
"template" sheet that has no entry in the invoice number cell - the code
in
the sheet's Activate event tries to put a number in it UNLESS:
there's already a number in it, OR
if the sheet name is the template sheet's name.
There is a button on the sheet to create new invoice which simply copies
the
'template' sheet which in turn activates the new sheet, which
automatically
assigns that new sheet the next invoice number in sequence.

All that's left to be decided, as far as I know, is which one of the two
PammiJ feels will work best with the way they do the work at their
establishment.



"Bob Phillips" wrote:

Have your code in a master workbook, with an invoice template worksheet
within it. Then copy that template to a new workbook, like so

ThisWorkbook.Worksheets("Template").Copy
With ActiveWorkbook
'your code
End With

just make sure that all of the code works on the actoive workbook from
that
point on.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Pammi J" wrote in message
...
Hi
i have a invoice template and with the help of JLatham i have managed
to
get
the invoice number to increment by one each time i open it. BUT i have
a
big
problem when i have filled out the invoice with customer details and
the
invoice number iss example 100 and then `save as` (cust name and no) it
seems
to be carrying the macro with it - so when i open cust name inv 100 it
becomes 101 - and then i open template that becomes 102 and so on.
I need it to save as per the village software invoice - saves just a
copy
to
wherever i specify so if i need to reopen that copy it doesnt change
the
invoice number.
Any help much appriciated