Non Live Cells in Excel

[TortolaChris]

A lot of cells in a worksheet are not live, i.e. figures within cells are treated as being numeric in that they can be referred to in calculations fine. However, they do not change when their format is changed e.g. when the decimal point setting is amended. Neither are they positively identified when referred to in array formulae.
The only way I have found of making these cells "live" is to reenter the data or by appearing to edit the cells by pressing F2.
Does anyone have a way of globally enlivening these cells or have an explanation as to why this is happening?
I hope the question makes some sense even if the condition doesn't!
Thanks for any help
Chris

 

[jonsi]

Check your settings in Tools>Options>Calculations ...sounds like Excel is set to Manual recalculation rather than Automatic.

hwyl
Jonsi ;-)

 

[TortolaChris]

Thanks Jonsi but that isn't it - the calculation is, and was, set to Automatic. I've pressed F9 for calc now jsut in case but with no affect.
Thanks anyway
Chris

 

[mbarron]

I'm guessing you're using Excel97. It seems to be quirky like you've described. You would think changing the format would change the cell instantly, but it doesn't. I've done hundreds of records (F2 - enter) on a sheet before finding the below solution.

Try this:
Enter 1 in a "free' cell. Copy it. Select the cells you want to change (or Ctrl+A for the entire sheet) Do an Edit/Paste Special... Pick "multiply" Viola! All of your numbers should be "live"

Mike

 

[TortolaChris]

Thanks Mike - that worked. I found that if I copied the culprit cells into word as unformatted text and pasted them back into Excel as text that that worked too. I'm more used to doings things that way so I can find replace stuff with tabs in Word so they appear in different columns when I paste back into Excel.
I'm using 2000 which is obviously just as quirky as 97!
Thanks again
Chris