AUTO NUMBER

[Guest]

HOW CAN I HAVE A AUTO NUMBER IN A CELL OF A EXCEL SHEET

 

[Frank Kabel]

Hi
not quite sure but the following formula will put a sequencing number
in column A, if column B is filled:

in A1 enter the following formula
=IF(B1<>"",1,"")

in A2 enter the formula
=IF(B2<>"",MAX($A$1:OFFSET($A2,-1,0))+1,"")
and copy this formula down for as many rows as you like

this formula will add a sequencing number in column a for all rows in
which
column B contains a value

 

[Guest]

I M SORRY I WANTED TO GET A AUTO NUMBER IN A TEMPLATE SO THAT WHEN SOMEONE USES THAT TEMPLATE THE CELL IS AUTOMATICALLY INCREMENTED

 

[Frank Kabel]

Hi
first you may turn-off your Caps lock as this makes your post difficult
to read and is also considered as SHOUTING in newsgroups.

Do you want to increment each time a new workbook is created based on
your template?. If yes this is a little bit complicated if you really
want to use an +.xlt file. You may explain with some more detail what
you're trying to achieve

 

[Guest]

I am sorry about that ..I shall explain wot excatly i m trying to do ..i want to make a template and wherever it is opened there is something called work order number and this is what i want to increase every time when the template is used.

 

[Frank Kabel]

Hi
one way (using the Windows registry for storing the last number). Put
the following code in the workbook module (not in a standard module) of
your template:
- It changes cell A1 of the first sheet
- you may change the key identifiert according to your needs (e.g.
DEFAULTSTART, MYLOCATION, etc.)

Private Sub Workbook_Open()
Const DEFAULTSTART As Integer = 1
Const MYAPPLICATION As String = "Excel"
Const MYSECTION As String = "myInvoice"
Const MYKEY As String = "myInvoiceKey"
Const MYLOCATION As String = "A1"
Dim regValue As Long

With ThisWorkbook.Sheets(1).Range(MYLOCATION)
If .Text <> "" Then Exit Sub
regValue = GetSetting(MYAPPLICATION, MYSECTION, _
MYKEY, DEFAULTSTART)
.Value = CStr(regValue)
SaveSetting MYAPPLICATION, MYSECTION, MYKEY, regValue + 1
End With
End Sub