Excel/Excel formulas

Advertisement


Question
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.

Gulshan.


---------- 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.

Thank you

Answer
Hello Bill,

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 - gulthemacroguy@gmail.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.

Thanks,
Gulshan.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.