INDIRECT() won't work for other sheets in one .xlsx file

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.

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.]

Test how the file has Menu/Tools/Options/LibreOffice calc/Formula/Formula Options/Formula syntax, to use the proper sheet separator. Dot for calc syntax, ‘!’ for excel.

Calc A1. So dot-type. And as I said, it works in new files.

…But playing with that idea. It turns out it was the syntax. It needed to be single quotes around the sheet-name and ! separator. Ie, =INDIRECT("‘Sheet1’!A1") even though Calc’s options are set to Calc A1.

Why is only INDIRECT() affected? In the same file, same sheet, =Sheet1.A1 works, as does =VLOOKUP(A1, Sheet1.A1:E15, 5)

There is another option in Menu/Tools/Options/LibreOffice calc/Formula/Formula/Detailed calculation settings - Custom - Default - Reference syntax for string reference.

For future reference:

The formula works (at least in Calc) if I change the syntax to…


Or, to add spaces for clarity, ( " ’ Sheet1 ’ ! A1 " ) Double quotes around the reference, single quotes around the sheet-name, and an exclamation point as the separator.

(Thanks to m.a.riosv for the clue.)