Freezing a value in a cell

[Jim Schenck]

How do you freeze a value in a cell in Excel?

Thanks,

Jim

 

[Frank Kabel]

hi Jim
what do you mean with 'Freeze'?

 

[JulieD]

Hi Jim

not 100% sure what you're after ... are you talking about making the value
unable to be changed, if so you need to enable worksheet protection -

choose all the cells that you want to change
choose format / cell / protection - untick "locked", click OK
choose tools / protection / protect sheet ... OK

now you can change the cells you selected in step 1, but can't change the
"locked" cells.

if this isn't what you're after please post back

Cheers
JulieD

 

[Guest]

placing a "$" in front of each coordinate will make the
value static, rather than moving about. Is that what you
mean? For example, by representing the formula as
$B$24+$C$35, the value will always calculate using B24 &
C25 no matter where that formula is placed.

 

[Patti]

There's probably an easier way to do it, but here's my
method...

I hi-light the worksheet, take off the protection lock
(Format, Cells, Protection, Locked), then hi-light the
cells I want to freeze, put the protection lock back on
those cells, and then protect the worksheet (Tools,
Protection, Protect Sheet). The locked cells are frozen
but the rest of the cells can be modified.

Another alternative is to use data validation --
choose "List" and enter the accepted value in the Source
box. Only that value will be accepted in the cell.

 

[Norman Harker]

Hi Jim!

To go with other guesses as to what you want.

Select the cell
Copy > Edit > Paste Special > Values > OK

This converts a formula entry to the value currently returned by that
formula.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[elliott123]

Hi,

I have a question around "freezing a cell".

I have the number of days in A1 and a total in A2.
Down column B i have the total (A2) divided by the number of days (A1). This continues down column B.

When i add a value into the C column on the same row as the value in the B column, i then want that B value to not change if i later change A1.

If i delete the value out of the C column, i then want the value in the same row, B column to change if i change A1.

Any help would be much appreciated. The simpler the better.

Cheers

 

[elliott123]

Column B is never entered manually so will protecting the sheet and unlocking/locking certain cells only prevent someone from typing over the cell?

Once a value is entered into column C, i need column B to act as if there is no longer a formula. That is the contents of column B are now a value.

Can this be done by "freezing" the formula in column B without actually deleting the formula and replacing with the value?


I dont know if the table below will come out when i post but column B is the column of 54,168, which is simply the formula total/#of days.

when i enter something into column C, 24,563, i then need the cell in column B beside it (54,168) to not change value if i later increase or decrease the number of days. The rest of the cells in column B that do not have a value next to them in column C still need to change if i update the number of days.

# of days = 23 .........54,168....... 24,563
Total = 1,245,863 .....54,168