Fill function replicates values but copies formulae

[SimonKravis]

For a particular spreadsheet, the fill function replicates the value from the first cell in filled cells rather than recalculating them from from cell formulae. The result of setting cell B1 to =A1 and filling down to B5 is the following:

	A	B
1	1	1
2	2	1
3	3	1
4	4	1
5	5	1

Clicking on cell B2 shows the formula =A2 as expected, and double-clicking and selecting the tick box updates B2 to the expected value of 2.

	A	B
1	1	1
2	2	2
3	3	1
4	4	1
5	5	1

The spreadsheet was in Excel 2003 format and viewed with Excel 2007, but the same behaviour was observed when saving to.xlsx format. Other spreadsheets show the expected behaviour when filling down from B1 to B5.

It seems as though the spreadsheet showing this behaviour has become corrupted in some way - is there any way to fix it other than transferring date to a new spreadsheet?

 

[Derek Brown]

Hello and welcome to The Board.
This is exactly what I would expect if the workbook calculation is set to manual instead of automatic - try Shift + F9 and if the formulas recalculate that will prove it.
You may not have knowingly changed the calculation method but Excel has a nasty little 'feature', whereby the calculation mode of the first opened workbook will be 'assumed' by other workbooks opened in the same instance of the applciation.
Have a look at:
http://support.microsoft.com/kb/214395

 

[SimonKravis]

Yes - this was indeed the problem. I'd received the spreadsheet to work on and being unaware of the possibility of disabling recalculation based on formulae never thought it might be a feature.

Thanks Derek