Formula for Deleting a Formula when the Formula has done its Calculation

[Sam_D]

Hello,

I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating.
I've got a formula
=IF(COUNTA(A1)=1,TODAY(),"")
When something is typed in Cell A1 the cell with the formula will input todays date.
Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.

[JBeaucaire]

This is most commonly done with a sheet-level macro. The macro would watch cell A1 and when a value is entered there matching your criteria, the macro puts a datestamp into another cell of your choosing. Is that OK?

If so:
1) What cell should receive the datestamp?
2) Is this a row-by-row function? Meaning if A1 changed, B1 gets a date, A2 >> B2, A30 >> B30 ...etc? Or is this just a one-cell thing?
3) What if someone CHANGES the value that is in A1 after a datestamp had already been created? Do you want the datestamp updated?

[Sam_D]

1) What cell should receive the datestamp? The date stamp will go into Cell B1
2) Is this a row-by-row function? Meaning if A1 changed, B1 gets a date, A2 >> B2, A30 >> B30 ...etc? Or is this just a one-cell thing? Row by Row
3) What if someone CHANGES the value that is in A1 after a datestamp had already been created? Do you want the datestamp updated? No, I would like the datestamp to stay at its orginal date.

[JBeaucaire]

1) Right-click on the sheet-tab where you want this behavior to occur
2) Select VIEW CODE
3) Paste in this macro code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'JBeaucaire (10/7/2009)  Add datestamp in column B
Dim cell As Range
Application.EnableEvents = False

For Each cell In Target
    If Not Intersect(cell, Range("A:A")) Is Nothing _
        And Cells(cell.Row, "B") = "" Then _
            Cells(cell.Row, "B") = Date
Next cell

Application.EnableEvents = True
End Sub

4) Close the VBEditor
5) Save your sheet

Try entering values anywhere in column A.

[Sam_D]

Thank you for your Help!
Will this give tomorrows date when the file is opened tomorrow?

[JBeaucaire]

Yes it will. The code inserts the "Date" whenever it triggers, whatever today's date may be.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[Sam_D]

Thanks for your help!