Bug 130333 - LibreOffice Calc: Invalid reference result from INDIRECT() function when trying to reference a cell in another sheet
Summary: LibreOffice Calc: Invalid reference result from INDIRECT() function when tryi...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2020-01-31 17:45 UTC by Bret Carver
Modified: 2021-03-15 16:12 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Bret Carver 2020-01-31 17:45:52 UTC
If I pass a parameter to indirect() that references a cell that is in a different sheet the result is an invalid reference [#REF!]

using indirect(B1) works but using indirect('Another Sheet'.B1) does not.

Steps to Reproduce:
1.In a cell enter =indirect(
2.Choose a different sheet and select the target cell
3.close the ) to finish the function and #REF! results

Actual Results:
Received #REF! in the cell instead of expected value

Expected Results:
Value entered into the target cell

Reproducible: Always

User Profile Reset: Yes

Additional Info:
Comment 1 Kevin Suo 2020-02-01 05:54:18 UTC
(In reply to Bret Carver from comment #0)

I can reproduce the "#REF!", but this seems to be not a bug.

1. Type "A2" in cell A1, sheet1
2. In another sheet, type in the formula "=indirect(<click cell A1 in sheet1>)"

Current Result:
(Since sheet1.A1 is blank, the value 0 is expected).

The "#REF!" error only appears when the referenced cell (i.e., sheet1.A2) does not contain any valid cell reverence.

Build ID: 0a6ec034dc8088d9de399142bb193ae7d338e645
CPU 线程: 4; 操作系统: Linux 5.4; UI 渲染: 默认; VCL: gtk3; 
区域语言: zh-CN (zh_CN.UTF-8); UI 语言: zh-CN
Calc: threaded

Am I doing something wrong?

I mark this as NEEDINFO. @Bret Carver: Would you please explain further.
Comment 2 m.a.riosv 2020-02-02 00:35:55 UTC
Or please attach a sample file where to test the issue.
Comment 3 ThomasU 2020-04-29 13:29:41 UTC
I notice that this bug occurs when working on Sheets imported from Excel into LibreOffice. Are your sheets originating from an Excel workbook?
I have posted this bug report if you respond positively:
See also: 
Comment 4 QA Administrators 2021-02-10 04:11:47 UTC Comment hidden (obsolete)
Comment 5 QA Administrators 2021-03-13 04:09:22 UTC Comment hidden (obsolete)
Comment 6 Eike Rathke 2021-03-15 16:12:08 UTC
Apparently this is if the sheet name is enclosed in typographical single quotes (e.g. ’ U+2019 RIGHT SINGLE QUOTATION MARK) instead of ASCII single quotes ' U+0027 APOSTROPHE. Disable Tools -> AutoCorrect, tab Localized Options, replacement of single quotes (and best double quotes as well); or make otherwise sure the correct characters are used to quote a sheet name. Note that comment 0 uses apostrophe, but I doubt that's what the cell actually contained. But as there never was sample file attached..