I have a particular file in which the INDIRECT() function gives a #REF! error whenever it is pointed to another sheet. For example: I have a sheet called “a”, and on the current sheet (not “a”) I enter =INDIRECT(“a.a1”) and I get a #REF! error. When I enter =a.a1 it returns the expected value. Likewise all other formula throughout the spreadsheet still work as expected when dealing with references to other sheets (eg, MATCH(), HLOOKUP(), etc). Only INDIRECT() fails.
But when I create a new file, INDIRECT() works as expected.
The spreadsheet is saved as an .xlsx, and moves back and forth between a Mac with MS-Excel and my own Win7 box with Libreoffice (v.5.3.7.2).
Are there any known bugs that could cause a spreadsheet to develop such a weird quirk? If so, is there a fix?
–
[I discovered this while trying to create a formula that worked with INDIRECT(). When it wouldn’t work I stripped the components back to individual formula, and discovered the culprit. I thought I was going nuts. I literally created a sheet called “a” just to eliminate the possibility of typos.]