Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there any way to embed the result of a formula evaluation in a cell, such that the evaluation only happens once?

For example: If I put =TODAY() formula in a cell today, if I open the spreadsheet tomorrow I want yesterday's date to be displayed in that cell.

share|improve this question

2 Answers 2

up vote 6 down vote accepted

In some cases where you want to get a static answer from a formula you can do what you want without using a formula.

In your example Excel doesn't know whether TODAY() was meant to mean today's date or the date that it was entered in the spreadsheet.

You can, however insert the current date and/or time in a cell: You can select a cell and press CTRL+; or CTRL+SHIFT+; for current date and current time respectively (for both CTRL+; then SPACE then CTRL+SHIFT+;).

In other cases, I tend to copy and then paste special as value.

share|improve this answer
    
+1 for paste special as value –  Adam Ryan Nov 15 '09 at 0:36

On the Tools / Options / Calculation menu set Iterations to one

Set up a new cell, lets say in A1, that is equal to itself plus 1 (yes, it is a circular reference)

Each time you calculate, by pressing key F9, you will see the value in A1 increase by one.

Put your formula inside an IF statement so that the your formula only executes when the A2 is equal to one; and is equal to itself when A1 is not equal to one.

So for example: Lets say your formula is in A2 your formula would be =If(A2=1,Today(),A2)

You can test this for yourself using Excel's Now function formatted to show minutes and seconds.

To reset the New Cell back to one, click in the formula bar and hit enter. (ie - re-enter the formula)

This should work provided you are not using Solver elsewhere in the work book.

Also note, that if you have other circular references in the workbook, Excel will not flag them as such.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.