auto increment invoice number/ID ?

[anamo]

hi all
the attached Template excel file has cell called "Invoice no."
I wan that cell to be automatically updated to give each invoice unique number

like S1000 and it ill be S1001 for the next invoice but i have no idea how this will be done


I tried to set it to be data and time using
=now() but the problem is that it shown by this format 7/31/14 10:59
i tried to covert it to this format 1407311059 but I fail


so please anyone can help !

 

[Chris Mack]

Maybe you could try using the row number in some way, e.g.,

="S"&TEXT(ROW(),"0000")

Or...

="S"&TEXT(ROW()+1000,"0000")

... would produce your example results.

Hope this helps,

Chris.

 

[anamo]

Maybe you could try using the row number in some way, e.g.,

="S"&TEXT(ROW(),"0000")

Or...

="S"&TEXT(ROW()+1000,"0000")

... would produce your example results.

Hope this helps,

Chris.

not really coz the cell row number is static it wont be change
the cell is static I just want the number to be automatically updated for each new invoice or like each time the template file saved new file

 

[Useful]

not really coz the cell row number is static it wont be change
the cell is static I just want the number to be automatically updated for each new invoice or like each time the template file saved new file

Hi try this (put this code Alt+F11/Thisworkbook module)
number will increase each time when you save wb

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
[A1] = [A1] + 1
[A1].NumberFormat = """S""0"
End Sub

 

[Chris Mack]

="S"&TEXT(NOW(),"yyyymmddhhmm")

This will change the cell value to the current date/time every time you save the workbook (or recalculate [F9]).

Hope it helps,

Chris.

 

[anamo]

="S"&TEXT(NOW(),"yyyymmddhhmm")

This will change the cell value to the current date/time every time you save the workbook (or recalculate [F9]).

Hope it helps,

Chris.

thanks

 

[anamo]

Hi try this (put this code Alt+F11/Thisworkbook module)
number will increase each time when you save wb

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
[A1] = [A1] + 1
[A1].NumberFormat = """S""0"
End Sub

kindly i'm not familiar with excel macro so if you can point me to how to do it

is it lable ?? button ??? how to use it please

 

[Chris Mack]

thanks

Yvw!

 

[Useful]

kindly i'm not familiar with excel macro so if you can point me to how to do it

is it lable ?? button ??? how to use it please

copy the code and go to excel workbook press Alt+F11 and from the left side you'll see Thisworkbook end of the VBAproject double click Thisworkbook and paste code there after this press Alt+Q and back to excel sheet and press F12 and save file as .xlsm or .xlsb type.
(after this each time when you save this workbook it'll increase automatically. In this case your Invoice ID inserts range A1)

 

[anamo]

copy the code and go to excel workbook press Alt+F11 and from the left side you'll see Thisworkbook end of the VBAproject double click Thisworkbook and paste code there after this press Alt+Q and back to excel sheet and press F12 and save file as .xlsm or .xlsb type.
(after this each time when you save this workbook it'll increase automatically. In this case your Invoice ID inserts range A1)

Okay done but this wont work with template excel files am I right ??
if yes then what you suggest to do that


thanks