I am having a very strange issue with automatically filling cells from one sheet to another. I am trying to do something that I have done before, but this time it is not working.

I have one set of sheets, which record all my income and expenses on an item by item basis, over a period of a fortnight. There is a "Totals" row at the bottom of each sheet, which contains functions totalling the items above.

I have another sheet which is intended to show the fortnightly totals from the fortnightly sheets.

The way I have done this in the past is:
1) I put "=" (equals sign) in the first cell in the totals sheet, navigate to the corresponding cell in the totals row of the appropriate fortnightly sheet, and hit "enter".
2) I then go back to the totals sheet, and the correct value has been entered as expected, as a formula (='Fortnight 13-09-12'!A24)
3) I then select that cell and fill accross the range of cells equivalent to the totals row in the fortnightly sheet - and this is where the problems happen. Instead of filling the range with the correct values, the row has been filled with dates. The forumulae in the filled cells are correct (e.g. ='Fortnight 13-09-12'!B24; ='Fortnight 13-09-12'!C24; etc) but instead of this resulting in values which are formatted the same as the target cells, all of them are formatted as dates (all of them in the year 1900).
  • I have checked, and all of the columns in the Fortnightly sheets are formatted correctly - as text, date or currency.
  • Before i did the filling, I formatted the columns in the Totals sheet to match the formatting in the target sheet.
  • After I have done the filling, if I then re-format the Totals sheet to match the target sheet, the values display correctly.
  • But if I then do exactly the same thing again (i.e. same totals from Fortnight sheet into the next row down in the Totals sheet, exactly the same problem happens - dates entered, not correctly formatted values.)

Can anyone help me sort out what is happening here?

I use Windows 7 64 Home Premium, Microsoft Office Professional 2010, Excel Version 14.0.6123.5001 (64-bit).