I am working on a business spreadsheet, and I was wondering, is there a way that selected cells can be programmed to reset at certain intervals?
So on a particular form, there are details of order numbers etc, but, would it be possible for excel to automatically reset the order numbers on a monthly basis?
I am using Excel for Mac 2011
Thanks in advance for your help.
Last edited by diggers16; 12-26-2012 at 10:32 AM.
Yes, but I think you would need to use VBA. You could use some code to activate on the sheet open event which checks the date and resets the cell accordingly.
I agree with bopsgtir. To do this requires macros. The code is not complicated, although you have to reserve someplace to record when you did the reset so you do exactly one reset each month. Here is what I would suggest. Note that I have no access to a Mac and am assuming that macros work the same way as they do in Office for Windows.
Create a new worksheet, name it "Last Reset", and then hide it. The user never has to see it.
The following code goes into the module called ThisWorkbook*. You haven't given any details about your data layout or what you want to reset so I can't give you a specific answer:
*To install this code, open your Excel file then press ALT-F11. This will open the VBA development window. On the left, you will see a tree of open Excel files, and under each one a list of worksheets. Find your file, and then underneath find ThisWorkbook. Double click on it. On the right side you will see the window for the code for ThisWorbook. Copy and paste this code. I do not know if Mac 2011 uses the XML format. If it does, Excel files are normally named .xlsx. You must save this file as a macro-enabled file, with an extension of .xlsm.
Private Sub Workbook_Open() With Worksheets("Last Reset") ' Test to see if the last refresh date has not been set, or it is prior to ' the current month If .Range("A1") = "" Or .Range("A1") < DateSerial(Year(Date), Month(Date), 1) Then .Range("A1") = Date ' put code here to do whatever "reset" you want to do End If End With End Sub
Thank you both for your help.
I have attached a screen shot of the sheet I am talking about, and all I want to do, is to be able to reset the values of 'Quantity Ordered' and 'Quantity Sold' columns on the first of every month.
Is this possible to do, using your code?
Thank you very much again, for your help.
Yes but what does "reset the values" mean? Do you mean "set all values to zero"?
Also please see the last line of my signature.
Yes, I mean reset them to 0.
And apologies, I'm sorry, I have attached the whole workbook, but I'm focussing on the "Product Orders & Stock" sheet.
Thank you for your help with this.
Here is the final product. I also corrected the typo on the column G header
Thank you so much for doing that. Hahaha, I didn't notice the header typo there
I guess I will find out if everything works tonight
Also, did you use macros to do this? As no macros are coming up on my list of macros for the spreadsheet.
Thanks Again!
Let me answer the last question first. Yes, it uses macros. Macros are required to do this. Post #3 above explains how to install the code, and you can use the same steps to view it. This code is in a module called ThisWorkbook, and is not visible when you look at available macros from the Excel interface.
When the workbook opens, the code runs and it checks the date in Last Reset. If that cell is empty, or if the date is from the previous month, then the code knows that a reset has not been performed in the current month. In that case it performs the reset, and then updates the date to be the current date. This way the reset is only performed the first time you open the workbook each month.
To answer your questions, the date in "Last Reset" is the most recent time the reset was performed. It is set by the code. You ran the code and did a reset today, so the next reset will occur the first time you open the file in January (which may be 26/1/2013, but also could be any other day in January).
If you manually change the date to 27/11/2012, yes it will force a reset the next time you open the file. Normally you never have to touch this, it's all automatic. Remember that the code will only run when you actually open the file, so it won't reset itself tonight--it will reset the next time you open the file. In general VBA code can only run when the file is open. This particular macro will only run at the moment the file is first opened.
"Last Reset" is hidden but there is no technical reason it has to be. You can leave it visible if you want. If someone else uses your file it might be confusing but that's up to you.
Ok, I understand it now.
Thank you for all of your time and effort you have invested in me. I really appreciate it, Thank You!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks