Excel Cross-sheet reference not working

Question

A cross-sheet reference within the same file is giving me difficulties.

There are merged cells at D5:G5 on the second tab, "Disclosures & Identification," that include text.

There is a reference to that cell on the first tab. "="Disclosures & Identification" is the formula in use. D5. The formula just shows up in the cell rather than transferring the content from the field on the second tab.

I have tried using the entire set of combined cells in the reference (d5:g5), renaming the tab (in case the & was the issue), saving the file under a new name and updating to Office 2007 format (old version was 2003, but that should have worked anyway), and doing what I believe to be an appropriate amount of searching for the solution (with no luck). Any suggestions as to why this could not be working are welcome.

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+`