Ask Your Question
0

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

asked 2018-02-16 11:35:20 +0200

Paul451 gravatar image

updated 2018-02-16 11:37:06 +0200

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Paul451
close date 2018-02-17 03:32:36.190526

2 Answers

Sort by » oldest newest most voted
0

answered 2018-02-17 03:30:42 +0200

Paul451 gravatar image

updated 2018-02-17 03:31:32 +0200

For future reference:

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

=INDIRECT("'Sheet1'!A1")

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

edit flag offensive delete link more
1

answered 2018-02-16 12:54:47 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Comments

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)

Paul451 gravatar imagePaul451 ( 2018-02-17 03:28:09 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2018-02-17 21:36:41 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2018-02-16 11:35:20 +0200

Seen: 390 times

Last updated: Feb 17 '18