Im working on a new `Time Sheet` for work and need a way of generating an order number.

At the moment I have used the format PK00001, PK00002 etc in a cell and have created a list (using data - validation - list) with a pull down menu to select the number used/required.

I have then obtained a macro (through google :-) ) to save the file as follows;

PK0001, Staff Member, Customer, Date

Macro Script;

Sub savesheets()

Dim newfile As String, ssno As String, eng As String, cust As String, loc As String

ssno = Range("J2").Value
eng = Range("E6").Value
cust = Range("e4").Value
loc = Range("j4").Value

newfile = ssno & " " & eng & ", " & cust & " " & loc & " Service Report.xls"
ChDir _
"C:\Documents and Settings\KHS\Desktop\Drafts"
ActiveWorkbook.SaveAs Filename:=newfile

End Sub

While this method is ok it would be better if there was a way excel could generate the number automatically as this does eventually need to be idiot proof.



Or is there a way of having a master file, which will save the edited file as a copy (see above macro) but will not let you use the same number twice?

If anyone has any suggestions of their experience or ideas, even if its not a sollution I would be greatful.

Thanks.

You could use a formula if you want. If you use this one, change the J10 to be the first StaffMember cell. Don't change the A1 references, they're there to produce a 1 but produce a 2 when copied (and then a 3, etc.) Copy down. The value will automatically generate as soon as data is entered into the StaffMember cell for that row.

=IF(LEN(J10)=0,"","PK"&REPT(0,4-LEN(ROW(A1)))&ROW(A1))

You can copy and paste values periodically if desired.

Another method: you could easily make a formula that would increment the value by 1 each row, and be formatted to display with PK and zeroes in front, but that would be displayed, not stored.

Thanks but this doesnt quite do what I wanted.

I need the serial number to include the initials, ie JB for joe bloggs PK for phil kennedy etc.

I have had an idea but I need to figure out a way to do it.

I know that you can copy cells to a txt file.

At the moment I have a list with all of my order numbers in (PK00001 - PK60000 or something :-) )

Is there a way, of copying the value used to a text file, then searching for and deleting that value from my list for next time

For example, if PK00001 is used, the value is copied to the txt file, then the value is searched for in the sheet and deleted.

Next the sheet is saved, this way the same number cant be used twice and once you have used 100+ numbers you dont spend ages searching through a lst.