Static value function

[rstultz]

I'm creating an option trading workbook with approximately 70 option trading strategy worksheets. Each worksheet uses functions that dynamically pull live market data into a series cells. To evaluate profit or loss, opening and closing prices must be compared. I'm trying to find a way to save the opening value in a second cell for later reference. Tried using the =VALUE() function, but of course the value changes as the value changes in the referenced cell. Searched Help for static and freeze functions, but these do not exist for dynamically changing values. Any ideas on how to "freeze" the opening value other than having the worksheet users type the opening market value into a cell?
Thanks,
Russ

 

[rstultz]

SOLVED WITH A MACRO!
I've worked out a fix by using a macro to copy and paste a the cell value rather than the formula. The macro uses the following keystrokes:


Point to Last Price in the "Current Price" cell.
Press Ctrl+C to copy the contents of the "Current Price" cell.
Move cursor to the destination cell.
Press Ctrl+Alt+V, type V, press Enter to paste the copied value.

 

[btorrean]

Russ,

Awesome! Glad you got it figured out! Just to let you know, in order to distribute VBA in a workbook, your users would not need to have Visual Basic installed. As long as you save your workbook as an ".xlsm" macro-enabled workbook, anyone with Excel can open and use it.

Brian

Brian J. Torreano