Bug 96666 - EDITING: Address Function incorrectly resolves when Sheet names is given to #REF
Summary: EDITING: Address Function incorrectly resolves when Sheet names is given to #REF
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2015-12-22 11:27 UTC by Trick Hartman
Modified: 2018-07-12 08:55 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Sample test (8.37 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-12-23 00:26 UTC, m.a.riosv

Note You need to log in before you can comment on or make changes to this bug.
Description Trick Hartman 2015-12-22 11:27:47 UTC
When trying to obtain the address using the following function:

=INDIRECT(ADDRESS(2,1, ,1,"Sheet1"))

I see a problem when the Formula Syntax is set to "Calc A1" between 4.7 and 5.0. Tools -> Options : LibreOffice -> Formula -> Formula Syntax = "Calc A1"

My file has two sheets:


In Sheet1 I have the following content in Column A

| Column A | Column B | 
| Bob      |          |
| Mary     |          |

On Sheet2, I have the following functions 

| Column A | Column B                             | 
| Mary     | =INDIRECT(ADDRESS(2,1, ,1,"Sheet1")) |
| Bob      | =INDIRECT(ADDRESS(2,1, ,1,))         |

In Version 4.7, the results on Sheet2 are 

| Column A | Column B | 
| Mary     | Mary     | <--- Expected Value
| Bob      | Bob      |

In Version 5.0, the results on Sheet2 are 

| Column A | Column B | 
| Mary     | #REF     | <--- HERE is the Error
| Bob      | Bob      |

I have confirmed this with version and I do not know if it is in the 5.x code base.
Comment 1 m.a.riosv 2015-12-23 00:26:33 UTC
Created attachment 121508 [details]
Sample test

Hi, thanks for reporting.

As I see the issue in your formula is that a space is not an empty parameter.

Please see the attached file, works for me with
Comment 2 m.a.riosv 2015-12-23 01:03:23 UTC
Seems the use of space is available again with:
Version: (x64) Build ID: bcace328aabc4c8c10b56daa87da0a2ee6579b5a
Threads 4; Ver: Windows 6.19; Render: GL;

Be aware that 5.1 is not ready for daily use, only for testing.

OTOH, is that formula is used intensively, can make slow the spreadsheet, because INDIRECT() is a volatile function.
Comment 3 Trick Hartman 2015-12-23 11:54:36 UTC
It is not the space that is the issue, nor INDIRECT. 

I see the error with the Sheet Name. When I use any sheet name, I get a #REF. Without it it works. 

Also, you used Excel R1C1 format, I was saying that when I use Calc A1 format. Please try to recreate again.
Comment 4 raal 2016-08-23 04:20:33 UTC
Seems to be fixed. Trick, please retest with actual version. Thanks

no repro with Version:
Build ID: 989e8bc0d792f0dc5778746fac45de129a22d7ac
CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-08-16_01:29:43
Comment 5 QA Administrators 2018-07-12 02:44:19 UTC Comment hidden (obsolete)
Comment 6 m.a.riosv 2018-07-12 08:06:00 UTC
Looks fine with:
Version: (x64)
Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: es-ES (es_ES); Calc: CL
Comment 7 Trick Hartman 2018-07-12 08:55:16 UTC
Version: (x64)
Build ID: c838ef25c16710f8838b1faec480ebba495259d0
CPU threads: 4; OS: Windows 6.19; UI render: GL; 
Locale: en-US (en_US); Calc: group

Problem is fixed in above version.