Bug 96666

Summary: EDITING: Address Function incorrectly resolves when Sheet names is given to #REF
Product: LibreOffice Reporter: Trick Hartman <trick>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: RESOLVED WORKSFORME    
Severity: normal CC: miguelangelrv, raal
Priority: medium    
Version: 5.0.3.2 release   
Hardware: All   
OS: All   
Whiteboard:
Crash report or crash signature: Regression By:
Bug Depends on:    
Bug Blocks: 108827    
Attachments: Sample test

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:

Sheet1
Sheet2

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 5.0.3.2 and 5.0.4.2. 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 5.0.4.2
Comment 2 m_a_riosv 2015-12-23 01:03:23 UTC 
Seems the use of space is available again with:
Version: 5.1.0.1 (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: 5.3.0.0.alpha0+
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: 6.0.5.2 (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: 5.4.7.2 (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.