Pull and store a value on specific date in another cell

[Guest]

What I have is a number in A1, which changes nearly every day. I would like
to set up a formula in, let's say A2 that says something along the lines of
pull the value and A1 on the 14th of the month and store that value.
I was thinking of something like:
=IF( DAY( TODAY() )=14, A1, "")
but obviously, the value obtained on the 14th is erased the following day. I
would like cell A2 to hold this value from the 14th for the rest of the
month. Thanks in advance for any help.

 

[Bob Phillips]

=IF( DAY( TODAY() )>=14, A1, "")


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

But what if the value of A1 changes nearly every day. I only want the value
that was in cell A1 on the 14th?

 

[Bob Phillips]

You could try some VBA

Option Explicit

Private Sub Workbook_Open()
On Error GoTo wb_exit:
Application.EnableEvents = False
With Worksheets("Sheet1")
If Day(Date) = 14 Then
.Range("B1").Value = .Range("A1").Value
ElseIf Day(Date) < 14 Then
.Range("B1").Value = ""
End If
End With
End If

wb_exit:
Application.EnableEvents = True
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)