+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    10-06-2009
    Location
    Blackpool, UK
    MS-Off Ver
    Excel 2003
    Posts
    29

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

    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.
    Last edited by Sam_D; 10-08-2009 at 03:45 AM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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?

  3. #3
    Registered User
    Join Date
    10-06-2009
    Location
    Blackpool, UK
    MS-Off Ver
    Excel 2003
    Posts
    29

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

    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.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    1) Right-click on the sheet-tab where you want this behavior to occur
    2) Select VIEW CODE
    3) Paste in this macro code:
    Please Login or Register  to view this content.
    4) Close the VBEditor
    5) Save your sheet

    Try entering values anywhere in column A.

  5. #5
    Registered User
    Join Date
    10-06-2009
    Location
    Blackpool, UK
    MS-Off Ver
    Excel 2003
    Posts
    29

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

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

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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)

  7. #7
    Registered User
    Join Date
    10-06-2009
    Location
    Blackpool, UK
    MS-Off Ver
    Excel 2003
    Posts
    29

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

    Thanks for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1