Excel: Cross-sheet reference not working

Question

I'm having trouble with a cross-sheet reference within the same file.

On the second tab named "Disclosures & Identification," there is a set of merged cells at D5:G5 which hold text.

On the first tab, there is a reference to that cell. The formula being used is "='Disclosures & Identification'!D5. Instead of copying the text from the second tab's field, the formula just appears in the cell.

I have tried renaming the tab (in case the & was the problem); I have tried using the entire set of merged cells in the reference (d5:g5); I have saved the file under a new name and updated to Office 2007 format (old version was 2003, but that should have worked anyway); and I have done what I consider an appropriate amount of searching for the answer (with no luck). Does anyone have an idea as to why this might not be working?

Answer 1

There are 2 possible things:

1. Make sure the format of the cell is not 'Text'. If it is, change to something else, such as 'General' and then double click in the formula bar and press enter.

2. Make sure that 'Show Formulas' is OFF. You can find the option under the tab 'Formulas'. Otherwise, you can use Ctrl+`