Stop Auto Update IF TODAY()

March 13, 2009 at 07:04:00
Specs: Windows XP
I have a spreadsheet which pulls through data from an FTE/Headcount spreadsheet if the week date is >= than a cell in the source spreadsheet. However once the source has been updated and a cell in the source SS containing =TODAY() changes I want to still keep the original data which has already been pulled through for the previous weeks which use the same formula. Is there any way to stop the auto update if the week date has already passed or is < the =TODAY() cell in the source SS? Currently using an IF function to no avail. Excel 2003

See More: Stop Auto Update IF TODAY()

Report •

March 13, 2009 at 08:21:38
I'm not quite sure which cell or spreadsheet you are trying to stop the update on, but here's a possible suggestion that might get you started. I'm just showing a concept here, not a solution to your specific issue.

For testing purposes, let's assume today is 3/13/09.
Put =TODAY() in A1 (to show 3/13/09)
Put 3/14/09 in C1
Right Click the sheet tab, choose View Code and paste the code below in the pane that opens.

The code will check the DateValue in A1 against the DateValue in C1 everytime a change is made to the sheet.

Since =TODAY() will return 3/13/09, which is less than the DateValue of C1, A1 will continue to contain =TODAY().

Now, change the formula in A1 to =TODAY()+1. Since the DateValue of A1 is now >= the DateValue of C1, the code will replace the formula with the hardcoded DateValue shown in A1.

Again, this is just a concept. It's up to you to determine which date you need to lock down so that updates no longer occur.

Private Sub Worksheet_Change(ByVal Target As Range)
 If DateValue(Range("A1")) >= DateValue(Range("C1")) _
        Then Range("A1") = DateValue(Range("A1"))
End Sub

Report •
Related Solutions

Ask Question