Loss of cell reference

I inherited a workbook from another worker. On sheet 2 a certain cell references a cell in sheet 1 and displays the contents of that cell. If I change the reference the cell only displays the new reference and not the contents of the cell referenced.

Example: On Sheet 1 cell C11 contains CAT and cell C12 contains DOG. On Sheet 2 a cell contains the reference =Sheet 1!C11 and shows CAT. If I change the reference to =Sheet 1!C12 the cell shows =Sheet 1!C12 rather than DOG. After it has been changed I cannot get the cell to accept another working reference. What setting or condition will caus this to happen?

Hi there,

The format of the cell is changing to text whenever you change the formula (probably because that's how the cell was formatted originally).

Firstly, you'll need to change the format of the cell to Custom by:

1. Ensure the cursor is on the cell in question
2. From the Format menu select Cells
3. Select the Number tab from the Format Cells dialog box
4. Select Custom from the Category list
5. Select the format of the cell from Type
6. Click OK

Then remove the single apostrophe ' at the start of the formula.

HTH

Robert

Thanks Trebor76,
I tried your solution but it doesn't really fix it. I thought it had, but it didn't stay reformated and I couldn't get it to work the second time.(?) Also, I'm sorry about the apostrophe on my example. The sheets in the workbook are named and to the best of my knowledge the apostrophe automatically goes with the "named" sheet, but I guess I just stuck it in out of habit of looking at the named sheets.
If you have any more ideas I would appreciate it.
Thanks again.