Bug 59712 - Function ISNUMBER() displays wrong answer when referencing a cell in another file
Summary: Function ISNUMBER() displays wrong answer when referencing a cell in another ...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.4.3 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2013-01-22 14:01 UTC by Bernheim
Modified: 2021-04-19 15:11 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
file a.ods (8.51 KB, application/vnd.oasis.opendocument.text)
2013-01-22 14:01 UTC, Bernheim
Details
ODS showing difference in ISNUMBER() behaviour for external / local references. (17.67 KB, application/zip)
2013-11-22 01:49 UTC, Owen Genat (retired)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Bernheim 2013-01-22 14:01:14 UTC
Created attachment 73450 [details]
file a.ods

Problem description: incorrect response of ISNUM()

Steps to reproduce:
1. FILE C.ODS
CELL A1: 22/01/2013
CELL A2: 24....
2 FILE A.ODS. ....
CELL A1 ='file:///E:/Dossiers/c.ods'#$Feuille1.A1
CELL A2 =ESTNUM('file:///E:/Dossiers/c.ods'#$Feuille1.A1)
CELL B1 ='file:///E:/Dossiers/c.ods'#$Feuille1.B1
CELL B2 =ESTNUM(A1)
CELL A3 =SI(ESTNUM('file:///E:/Dossiers/c.ods'#$Feuille1.A1);'file:///E:/Dossiers/c.ods'#$Feuille1.A1;"rien")
CELL B3 =SI(ESTNUM('file:///E:/Dossiers/c.ods'#$Feuille1.B1);'file:///E:/Dossiers/c.ods'#$Feuille1.B1;"rien")

Current behavior:
CELL A1 22/01/2013
CELL B1 24
CELL A2 VOID  (NOTHING VISIBLE)
CELL B2 TRUE
CELL A3 "rien"
CELL B3 "rien"

Expected behavior:
CELL A1 22/01/2013
CELL B1 24
CELL A2 TRUE
CELL B2 TRUE
CELL A3 22/01/2013
CELL B3 24

with OPENOFFICE 3.41 the bug doesnot appear             
Operating System: Windows 7
Version: 3.6.4.3 release
Comment 1 Owen Genat (retired) 2013-11-22 01:33:33 UTC
The text in the original description exhibited several problems:

- Only file a.ods is attached.
- Description references A.ODS and C.ODS.
- Attachment contains formula directory references "~/Le Pro/Dossiers/b.ods" which are not indicated in the description.
- Cell references are not clear or consistent.

These points made using the original example unworkable. I have set the component to Spreadsheet (from Writer) and edited the summary for greater clarity. I will attach a clearer example that better exhibits the problem, as it does appear to be a problem.
Comment 2 Owen Genat (retired) 2013-11-22 01:49:24 UTC
Created attachment 89610 [details]
ODS showing difference in ISNUMBER() behaviour for external / local references.

Tested under Crunchbang 11 x86_64 running v4.1.3.2 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a. The two files in the attached should be self-explanatory, but basically:

- a.ods references source data in b.ods and compares against local references.
- b.ods source data (B1:B4) containing some basic numeric and non-numeric data.

External references of the type =ISNUMBER('file:///path/to/file.ods'#$Sheet1.B1) appear to return FALSE regardless of data type, while local references of the type =ISNUMBER(B1) return TRUE or FALSE as expected i.e., according to data type.
Comment 3 Owen Genat (retired) 2013-11-22 01:50:04 UTC
Status set to NEW.
Comment 4 QA Administrators 2015-04-19 03:20:28 UTC Comment hidden (obsolete)
Comment 5 Buovjaga 2015-06-15 12:13:16 UTC
(In reply to Owen Genat (retired) from comment #2)
> Created attachment 89610 [details]
> ODS showing difference in ISNUMBER() behaviour for external / local
> references.
> 
> Tested under Crunchbang 11 x86_64 running v4.1.3.2 Build ID:
> 70feb7d99726f064edab4605a8ab840c50ec57a. The two files in the attached
> should be self-explanatory, but basically:
> 
> - a.ods references source data in b.ods and compares against local
> references.
> - b.ods source data (B1:B4) containing some basic numeric and non-numeric
> data.
> 
> External references of the type
> =ISNUMBER('file:///path/to/file.ods'#$Sheet1.B1) appear to return FALSE
> regardless of data type, while local references of the type =ISNUMBER(B1)
> return TRUE or FALSE as expected i.e., according to data type.

Confirmed.

Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+
Build ID: 01a189abcd9a4ca472a74b3b2c000c9338fc2c91
TinderBox: Win-x86@39, Branch:master, Time: 2015-06-14_07:46:28
Locale: fi-FI (fi_FI)
Comment 6 QA Administrators 2018-07-25 02:40:30 UTC Comment hidden (obsolete)
Comment 7 QA Administrators 2020-07-25 04:00:19 UTC Comment hidden (obsolete)
Comment 8 Andreas Heinisch 2021-04-19 15:11:42 UTC
Works with:

Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 9e666c8fb3a361d0d6bd8bcb839b9d2a7485fbdb
CPU threads: 6; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: en-US
Calc: CL