Protecting specific cells from recalculation.

[tigermuppetcut]

Hi I know I can set calculations to be manual or automatic but can
highlight a bunch of cells and make them manual recalculation onl
while leaving the rest of the sheet on automatic?

The reason being I generate random numbers then have a drop down lis
in other cells to assign those numbers, but the drop down list remove
options as they are used (the list gets exhausted) to preven
duplication / re-use of the generated numbers when they are assigned
this requires automatic updates to work, but when that happens th
random numbers get regenerated.

So can I lock those numbers somehow, protecting them from all but an F
re-calc while leaving the rest of the sheet under automatic re-calc?

Thanks

 

[Niek Otten]

Copy them and paste them back in their original location, using Paste Special, Values

 

[joeu2004]

this requires automatic updates to work, but when that
happens the random numbers get regenerated.

Ain't that a bitch!? I don't know why RAND() violates the
general rule that only affected formulas are recalculated
after modification. I can only guess that it was someone's
bad idea long ago, and it persists today in deference to
compatibility. (Screams for yet-another Tools > Options.)

Anyway, I avoid the problem by creating a simple macro
function, as follows:

function myrand(optional rnge) as double
myrand = rnd()
end function

If you are unfamiliar with macros, you enter this by typing
alt-F11 and, if necessary, click Insert > Module, then type
and edit. You can invoke the macro in any of three ways:

myrand() ' recalc "only" if ctrl-alt-F9 is pressed
myrand(A1) ' recalc if A1 changes
myrand(A1:C1) ' recalc if any cell in A1:C1 changes

There are other exceptional times when myrand() is
recalculated, namely when a worksheet is inserted or
deleted, I believe :-(.