Look at a cell once

[fredthedog]

Hi I could really do with someones help.

Cells
A1 - this cell has a countdown to a certain date (63 weeks until tax is due) and will change every week automatically.
A2 - data input cell
A3 - I would like this cell to =IF(A2="","",A1) however I don't want it to change weekly like A1 does, so I want it to look at A1 and copy 63 weeks and next week when A1 can ages to 62 weeks I want a3 to still say 63 weeks.

Is this possible?

 

[fredthedog]

Hi I could really do with someones help.

Cells
A1 - this cell has a countdown to a certain date (63 weeks until tax is due) and will change every week automatically.
A2 - data input cell
A3 - I would like this cell to =IF(A2="","",A1) however I don't want it to change weekly like A1 does, so I want it to look at A1 and copy 63 weeks and next week when A1 changes to 62 weeks I want a3 to still say 63 weeks.

Is this possible?

 

[offthelip]

You can do this easily with a bit of vba in the worksheet change event for that worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
 If Target.Value <> "" Then
Application.EnableEvents = False
Cells(3, 1) = Cells(1, 1)
 End If
Application.EnableEvents = True
End If
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

 =IF(A2="","",63)

 

[fredthedog]

Hi & welcome to MrExcel.
How about

 =IF(A2="","",63)

There is a few data fields that will be filled out through out the year so I need it to look at A1 on that day and keep it if that makes sense?

Basically what you said would work this week but wouldn't work next week as the A1 cell would say 62.