Convert formula to value based upon date

[Guest]

Here is what I want to do:
Cell1 references Cell2. Cell2 is a number that is pulled from another
worksheet. The value in Cell2 can change based upon new information entered
into the other worksheet. Cell1, however, should retain the old information
from Cell2 if TODAY() is > than the date at the top of the column in which
Cell1 resides. If TODAY() is < than that date Cell1 should update. I'm
really not sure how to make this happen! Help much appreciated. I've
thought about enabling manual recalculation, but this won't work for other
reasons.

 

[Dave Peterson]

I think I'd use an event macro that fires each time the worksheet recalculates.

If you want to try, rightclick the worksheet tab with cell1, cell2, and the date
cell on it. Select View code and paste this into the code window.

Option Explicit
Private Sub Worksheet_Calculate()

Dim Cell1 As Range
Dim Cell2 As Range
Dim DateCell As Range

Set Cell1 = Me.Range("a2")
Set Cell2 = Me.Range("b2")
Set DateCell = Cell1.EntireColumn.Cells(1)

If Date > DateCell.Value Then
'do nothing
Else
Cell1.Value = Cell2.Value
End If

End Sub

Then close this window and back to excel and try it out (by changing the cell
that cell2 uses) (and the date cell, too.)

 

[mjp]

Thank you. I'll give that a try!

 

[mjp]

This blanks the cell that I'm trying to preserve when it is supposed to
'do nothing! Suggestions?