I am using Excel to produce work orders and I need each one to have its own unique number. Much like an invoice or contract number, each time I open the file I would like Excel to generate a new number for that spreadsheet with a value of 1 more than the previous spreadsheet. Does anyone know if this is possible?
There's a question here about knowing what the new work order number is.
How would anybody - you, me or excel; know the number of the previous work order?
For example - if you're creating work order 52. How would we know that work order 51 exists? Do you file them in a particular directory on your PC or is there some sort of central log?
If there's no system like this, it's very hard for anyone to generate a new work order number.
Let me know.
This is frequently asked, and there are several ways to do it.
Depending on your level of experience, the easiest implementaion is the registry, and the most complex in the add-in.
- If numbers are assigned only on your computer,
- You can create a registry key that you increment each time you need a new number.
- You can use an ad-hoc add-in that stores the number on a hidden sheet.
- You can use an ad-hoc workbook or text file on your machine or a network share. For a workbook, open the file, grab the number, increment it and save the file. For a text file, open for read access, get the number, close the file, open for write access, increment the number, close the file.
Last edited by shg; 07-02-2008 at 03:39 PM.
Shg,
Yes, that's what I would like to do. Please tell me how. I have a little experience with Excel but my knowledge is not that broad.
Which one?
The easiest. Although I may have to have multiple users use the same spreadsheet, I would like to figure out how to do it on my own system first.
I used the codes as advised but i am having an issue with the numbering and would it be possible to stop the sequence or how best to start from the scratch
Rather than trying track the last ticket number and increment it which has to be stored somewhere reliable between when the system is shut down and restarted , i use the date and time to assign a ticket number which ensures I never get a duplicate ticket number. The ticket number is dependend on the date and time to the 6th decimal place. Depending on the expected minimum length of time between your program assigning work order nos, you can change the granularity up or down by changing the 6.
Function AssignWONumber() as string dim dtserial as long, tmserial as double, i as integer dtserial = DateValue(a): tmserial = TimeValue(a) i = InStr(str(tmserial), ".") AssignWoNumber = Trim(str(dtserial)) + Mid(str(tmserial), i + 1, 6) exit function
Yes.I was shocked to see when I opened the template one day that the number had increased by 10. Is their opening the *.xls file on their system also increasing the number?
Keep the process manual to eliminate your headaches. May not be as elegant but its functional.
bello_e, EssoExplJoe, Logit,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Ben Van Johnson
protontLeah:
Must be a server issue. When I posted my response it was immediately after Shmoedoe's #15 post. I specifically recall because his commentwas what prompted me to respond.Is their opening the *.xls file on their system also increasing the number?
Unusual that three different people would post to the wrong thread ... hmmm
Auto generating work order number
Started by Shmoedoe, 07-02-2008 05:45 AM
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks