I need to derive a value in a cell by formula, and then hold that value
somewhere unchanged when the input values are later updated. Is this
possible?


--
JK57
------------------------------------------------------------------------
JK57's Profile: http://www.excelforum.com/member.php...o&userid=33430
View this thread: http://www.excelforum.com/showthread...hreadid=537540

I guess it looks like the answer is no.


--
JK57
------------------------------------------------------------------------
JK57's Profile: http://www.excelforum.com/member.php...o&userid=33430
View this thread: http://www.excelforum.com/showthread...hreadid=537540

You would need an event macro to do this.

Post your question in the Programming forum and include more details. That's
one of the reasons you didn't get any response.

Biff

"JK57" wrote in message
...

I guess it looks like the answer is no.


--
JK57
------------------------------------------------------------------------
JK57's Profile:
http://www.excelforum.com/member.php...o&userid=33430
View this thread: http://www.excelforum.com/showthread...hreadid=537540

The answer is yes, but it depends on what you want to do and what
degree of automation you desire.

First, we have menu Edit|Paste Special... In there you can select
Values and thus forget the formula. If you want to automate this
process, you can record it once, with Tools|Macros|Macro Recorder and
then use it with a button (right-click on menu area, activate Forms
toolbar).

Another solution involves formulas with circular reference.
- In a flag cell, say K5. enter 0.
- In your storage cell, say, K6, enter the following formula:
=IF(K5=0,K6,A2)
where A2 holds your input cell.

With the second method you can set K5 to 1 when you want the currently
displayed value in A2 to be stored. Immediately afterwards you return
the flag to 0, which freezes the stored number until flag is again
non-0, and so on.

The second method is a bit tricky, since it involves changing the
default calculation settings of Excel, which normally would not allow
such formulas, since they are self-referntial.

However, you can use Tools|Options|Calculation tab to check the
Iterations option ON and choose number of Iterations to 1. Normally
this setting should be reverted to Iterations OFF, since it would
affect the entire application. Under the ON setting, it is possible for
a user to build a wrong formula unintentionally, as for example
puntting in A10 the formula =SUM(A1:A10).

To guard against this possibility, it is advisable that you use two
worksheet event macros for the sheet in which you want this storage.
The Activate event macro can set Iterations setting to on when the
sheet is activated. The Deactivate will revert to typical calculation
mode when another sheet is selected.

You can paste the following code in you sheet's code page (Right-click
sheet tab, choose Vew Code... and paste it in the ocde window in the
VBA IDE that will appear). To be absolutuly sure, paste the body of
Deactivate in the Workbook_BeforeClose event macro too, double-clicking
the icon of ThisWorkbook in the IDE.

Private Sub Worksheet_Activate()
With Application
.Iteration = True
.MaxChange = 0.001
End With
End Sub

Private Sub Worksheet_Deactivate()
With Application
.DisplayAlerts = False
.Iteration = False
.MaxChange = 0.001
.CommandBars("Circular Reference").Visible = False
.DisplayAlerts = True
End With
End Sub

HTH
Kostis Vezerides