I am experiencing some bizarre behaviour when entering a formula. Below are screen shots of each stage of the process.
I have a sheet with a Summary list of values and a total:
Cell F8 is selected as you can see in the address bar/cell name at top left. As highlighted the cell is formatted as General.
I enter a formula in cell F8 pulling the total from a Data sheet. This total should be the same as the total in B8. Screen shot below:
As you can now see the cell format has changed to Text. Why??? But the value of the SUM formula is correct. Realising that the two total values are not the same, I edit the SUM formula to deduct the other total:
Edited formula only shows formula in cell and not the result; because it is now text but why did it change?
The only thing I can see is that the header of column C on the Data sheet, ie the first cell in the SUM range is formatted as Text. If I change the header cell to General, it works properly; ie it doesn't change the SUM to text.
I know excel is clever enough to change the format of a cell when you first enter a formula, eg if a cell referred to is a date, it will change the formula cell to a date. Is it not clever enough to realise that it doesn't need to change in this instance or at least can't determine what format to use so does nothing?
Any suggestions? A bit of a quandry for me.