Bug 138070 - CELL function does not work as described.
Summary: CELL function does not work as described.
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All Windows (All)
: medium minor
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2020-11-08 12:43 UTC by Jerzy Moruś
Modified: 2020-12-23 15:42 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Result of using the CELL function with a range reference (13.39 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-11-08 12:43 UTC, Jerzy Moruś

Note You need to log in before you can comment on or make changes to this bug.
Description Jerzy Moruś 2020-11-08 12:43:19 UTC
Created attachment 167097 [details]
Result of using the CELL function with a range reference

The function description is as follows:Returns information on a cell such as its address, formatting (...). Reference is the address of the cell to be examined. If Reference is a range, the cell reference moves to the top left of the range. If Reference is missing, Calc uses the position of the cell in which this formula is located. 

This description is discordant for 2D range and vector's range.
When reference is a 2D range, result is allways  #VALUE!. When reference is a vector, the result is displayed only when the formula is stored in the rows or columns corresponding to the range.
An example is attached
Comment 1 Martin Srdoš 2020-11-12 20:06:23 UTC
Hello Jerzy,

Thank you for reporting the bug.

In the function CELL() is a mistake. The string must be between quotation marks, eg "string". And I won't see any defined range in your document. I can't say why there is a number in some cells and #VALUE in some. However, that is the only thing that is wrong.
Please provide a clearer set of step-by-step instructions on how to reproduce the problem.
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the steps are provided
Comment 2 Jerzy Moruś 2020-11-12 21:13:09 UTC
Function CELL have a 2 arguments. First is "type of information" and must be written in quotation marks, but only when is written directly like text. In my example this arguments are written in first row (from column F to I) and are passed as a cell reference. 
The ranges are colored cells. They have names Opel, Opel2 and Opel3 (look at Sheet -> Names cells adnd references).

Reproduce the problem. In cell e.g. E12, write formula =CELL("address";A1:B2)
The description of the function says that the result should be the address of cell A1 (upper left corner of the range). However, this is not the case and we get #VALUE !
The results (but incorrect, not the top right corner) appear only for a vector region, only in cells that match the rows or columns of the vector (see attatchment).
Comment 3 Martin Srdoš 2020-11-13 12:06:16 UTC
Thanks for the message Jerzy,

I can confirm that it depends on that in which cell the CELL () function is used against the source data. If the reference is horizontal it only works as in described only if it is used in the same columns, otherwise it returns #VALUE. If the reference to cells is vertical, it only works if it is in the same rows, otherwise it returns also #VALUE.

The description of the function, is here "https://help.libreoffice.org/Calc/Information_Functions#CELL".

I reproduced the bug in versions:

[Version: (x64)
Build ID: 00e5c63c35307faacf76a5e2ca7953c4208244ed
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: cs-CZ (cs_CZ); UI: en-US
Calc: CL]

ID sestavení: 1b3956717a60d6ac35b133d7b0a0f5eb55e9155]