boy, this is cute!
when I posted my question I was convinced there is no way it could be done,
but this is cool ...

JE McGimpsey wrote:

Sure, as long as you don't mind using a bit of VBA.

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A2" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=prevDay(A1)"
Application.EnableEvents = True
End If
End If
End With
End Sub

When you overwrite the formula, the value you input will stick. When you
delete that value, the code above will put the formula back into the
cell.



In article , Mark Dvorkin
wrote:



A weekly workbook has 7 sheets, one for each day.

My function say in cell a2 (=prevDay(a1)) puts the value
from cell a1 of the previous day into cell a2.

Sometimes I need to overwrite this value manually.

Is there a way of doing this without loosing
the formula in a2?

thanks in advance,
/mark