Hi Bernie,
sorry for being greedy but I forgot something that would be o great help.
Can these ranges A2:A21 and B2:B21 be dynamic. Point is that for one exercize
date, e.g. Oct, I may have options with strike prices 52, 54, 56 and 58, but
for Nov options series strike prices may increase or decrease, say, 56, 58,
60, 62, 64 and 66. Thanks again.
--
Dan GSB
Asset Management - Rio


"Bernie Deitrick" wrote:

Dan,

For simplicity and maintainability, move all your data links into one table, on a sheet named
"DataLinks".

Let's say that you have the links starting in cell A2, down to A21. In B2, use the formula

=A2

and copy that down to B21. Now you will have a 'doubled' table of data.

Here's the key point - Link all of your cells needing the values from the data links to the cells in
column B.

When you want to freeze the values, run this macro:

Sub Freeze()
Worksheets("DataLinks").Range("B2:B21").Value = _
Worksheets("DataLinks").Range("B2:B21").Value
End Sub

When you want to unfreeze the links, run this macro:

Sub UnFreeze()
Worksheets("DataLinks").Range("B2:B21").FormulaR1C 1= _
"=RC[-1]"
End Sub

The links in column A are unaffected by these manipulations.

Assign the macros to buttons, and perhaps write the state to another cell, along the lines of:

Worksheets("Other Sheet").Range("StatusCell").Value = "Values FROZEN"
or
Worksheets("Other Sheet").Range("StatusCell").Value = "Values Active"


HTH,
Bernie
MS Excel MVP


"DanGSB" wrote in message
...
Hi,
how can I "freeze" a given value of a cell which is updated on a realtime
basis, e.g., stock options prices imported from a sort of Bloomberg data
diffusion service? I have a worksheet that evaluates options strategies for
10 options series calculating implied volatility on a realtime basis. What I
need is, once the trader decides to enter a strategy, beginning prices must
be secured (values must be "frozen", de-activating the underlying updating
formulas), but all the same these very formulas should be restored once the
strategy is closed and options positions are zeroed, which happens maybe in
the very same day, or next week. I have 20 of such cells spread throughout
the workbook with 10 worksheets in Excel 2007. Any thoughts?
Thanks very much for any help.
--
Dan GSB
Asset Management - Rio