Stop formula Auto update on 1 sheet

[afinite]

Hi guys,

I have an excel file which uses formulas to display the report's month based on system date. Unfortunately, if someone opens this file in the next month, the value changes...is there any way to stop auto update of this formula after that initial update? or stop auto update of formula on just that 1 sheet (I have other sheets too so cannot turn off formulas completely) ?

 

[amazing_wonders]

Not sure if this is what you were going for but you could enter the following code in the VBA module for the workbook and replace the sheet name to match yours and the cell location containing the system date that you are trying to retain on the month advance.

Private Sub Workbook_Open()
Dim shtdte As Date
shtdte = Sheets("Sheet1").Range("A1").Value
If Month(Now()) > Month(shtdte) Then _
Sheets("Sheet1").Range("A1").Value = shtdte
End Sub

 

[excel_help_101]

Could you do something along the lines of when your done with the updates run a Macro to Copy and Paste Just the values over top the Formulas there for they will never change??

 

[afinite]

Not sure if this is what you were going for but you could enter the following code in the VBA module for the workbook and replace the sheet name to match yours and the cell location containing the system date that you are trying to retain on the month advance.

Private Sub Workbook_Open()
Dim shtdte As Date
shtdte = Sheets("Sheet1").Range("A1").Value
If Month(Now()) > Month(shtdte) Then _
Sheets("Sheet1").Range("A1").Value = shtdte
End Sub

Thanks I will try. Could you explain what this does? Never used VBA module..how do I do this on excel 2010?

 

[afinite]

@excel_help_101 - Not sure how to setup such macro

 

[excel_help_101]

i was just thinking something like this
Sub Test()
'
' Test Macro

'this is the Range that you want to stay the same
Range("D4:D16").Select
Selection.Copy
'then you past the Values over top the old formats
Range("D4:D16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("D4").Select
End Sub

 

[amazing_wonders]

Just open your workbook and hit Alt+F11. That will bring up the VB screen. Double click "ThisWorkbook" in the left pane under the heading (VBAProject(your file name here). Paste in the below code into the right side and save your file. He will work automatically each time you open your workbook. I have added comments to each line of code to explain what each one is doing.

Private Sub Workbook_Open()
'Declares variable as a date
Dim shtdte As Date
'sets variable to the date you are checking in your sheet
shtdte = Sheets("Sheet1").Range("A1").Value
'if current month is greater than the date in your workbook then
If Month(Now()) > Month(shtdte) Then _
'it puts in the older date that was there without the formula
Sheets("Sheet1").Range("A1").Value = shtdte
End If
End Sub

 

[afinite]

Thanks for the detailed explanation! I followed the instructions but the date is still updating for some reason.

To clarify, this is what I want to do:

Current Month - August 2012 (set using the formula: =TEXT(TODAY(),"mmmm yyyy"))

When I open this excel file in September - this cell gets updated automatically to September 2012. I want to prevent this from happening in older files so that when someone opens an august file in spetember - the report month still says august in that file.

Unfortunately I cannot turn off formulas as some other sheets in my file rely on them and they get disabled if I do.

Thoughts?

Just open your workbook and hit Alt+F11. That will bring up the VB screen. Double click "ThisWorkbook" in the left pane under the heading (VBAProject(your file name here). Paste in the below code into the right side and save your file. He will work automatically each time you open your workbook. I have added comments to each line of code to explain what each one is doing.

Private Sub Workbook_Open()
'Declares variable as a date
Dim shtdte As Date
'sets variable to the date you are checking in your sheet
shtdte = Sheets("Sheet1").Range("A1").Value
'if current month is greater than the date in your workbook then
If Month(Now()) > Month(shtdte) Then _
'it puts in the older date that was there without the formula
Sheets("Sheet1").Range("A1").Value = shtdte
End If
End Sub

 

[amazing_wonders]

Could you give me a better understanding of what the date is representing by using a formula? Is it show the date when the file was created? Is it a date that needs to advance a certain number of days before becoming static?

 

[afinite]

Could you give me a better understanding of what the date is representing by using a formula? Is it show the date when the file was created? Is it a date that needs to advance a certain number of days before becoming static?

Basically, the report is sent out to users and the date represents the month the report was sent in. I have used formulas because there are multiple reports and it saves manual work to enter dates manually each month.

I have 2 formulas in these reports: 1) shows current month and (2) calculates current month - 1 month.

Example (for a report I am sending out in august):

Cell A - August 2012
Cell B - July 2012

Problem is that if someone opens an older report - the formula recalculates the date and updates the cell values. I am looking to prevent this somehow...