How do you add 1 to an invoice number in Excel. Formula for this?
Greg,
Here is my re-written text. Can you give me feedback if this explains it
better.
Thanks
Bob
Here is a technique to add an incrementing id to a workbook, one which
increments each time that the workbook is opened. This can be very useful
for maintaining invoice numbers, tracking changes etc.
In essence, this technique uses an Excel name, which doesn't refer to a
range, but to a number. You could define this name yourself in your
workbook, and add code to increment it, but this technique does all of that
work.
Once this code is installed, the incrementing id can be accessed as follows
In a worksheet
=__UniqueId
In VBA
Evaluate(ThisWorkbook.Names("__UniqueId").RefersTo )
Not that the name of the Excel name is defined as a constant within the
code. I use __UniqueId, but it can easily be changed to whatever you want to
use.
Implementation
This is workbook event code, so it needs to be added the to the ThisWorkbook
code module within the workbook.
To input this code, right click on the Excel icon on the worksheet (or next
to the File menu if you maximise your workbooks), select View Code from the
menu, and paste the code in the code pane that pops up.
Private Const sIdName As String = "__UniqueId"
'-------------------------------------------------------------
Private Sub Workbook_Open()
'-------------------------------------------------------------
GetId
End Sub
'-------------------------------------------------------------
Private Sub GetId()
'-------------------------------------------------------------
Dim myId As Long
myId = 1 ' in case it doesn't already exist
On Error Resume Next
myId = Evaluate(ThisWorkbook.Names(sIdName).RefersTo) + 1
ThisWorkbook.Names.Add Name:=sIdName, RefersTo:="=" & myId
End Sub
'-------------------------------------------------------------
"Bob Phillips" wrote in message
...
Talk about crossed wires, I thought you meant the F4 function key <vbg
No, when I said define a name, I meant doing it in code, and that is what
is
happening in this lien
ThisWorkbook.Names.Add Name:="__UniqueId", RefersTo:="=" & myId
So you need to use that name __UniqueId.
Oh, and it doesn't need to be tied to a cell, it is a name with just a
value.
I'll re-write it tomorrow (bed-time) and try and explain it better.
--
HTH
Bob Phillips
"GregR" wrote in message
oups.com...
Bob, what it means is I have defined "F4" by the user name "Inv". I
thought that's what you meant by "define a name in your workbook". HTH
Greg
|