QUESTION: Can I create a formula that will increase by one each time a file is saved? Such as a purchase requisition form.
ANSWER: Hello Bill,
I'll need more specific information than a single statement question. To start with:
1. Are you saving this file as a separate file every time (Using save as) or just saving.
2. Do you want to increment the number every time the file is saved or only when its saved and closed. Eg. if the file is open for a long time and you saved it a few times in between while working on it, do you want the number to be incremented?
3. I'm guessing you are trying to automate the number of one of your processes like PRN, Invoices etc, if that's the case, then you'll need to also control the validity of these numbers. Eg. someone saved the file but never issues the PRN, in that case you'll have a skipped number the next time you try to use the file.
If you can make your question more specific with the required end results, I'll be able to help better.
---------- FOLLOW-UP ----------
QUESTION: 1. Save as separate fie each time.
2. Saved and the number would stay at original assigned number.
3. You are correct to automate PRN and number validity would be based on yr/month/date + assigned number.
I think I have a solution for you, but because there is a certain amount of logic involved, I need to use macros. If you are fine with macros as a solution and can reasonably maintain them in the future, then I have the below proposal for you:
1. You have a template for PRN
2. I'll create a button on this template file.
3. Everytime you need to create a new PRN, you will open this template file and enter all the details.
4. Once you click the button, excel will automatically assign a PRN, create a new file and save it in a path of your choice.
5. I'll need to keep a cell (probably hidden) in the template file which will record the last PRN number generated. This is required to keep incrementing the PRNs automatically.
If you agree with this approach, please send me a sample file to work with(after removing any confidential information / data).
You can please send it to - email@example.com and include "AllExperts" in the subject line.
If you want to avoid the macro and go the formula way, then I'm not sure how I would solution it because I wouldn't have an automated way of storing the last PRN assigned.