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
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
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).
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: 7.1.0.0.alpha1+ (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] [Verze: 4.1.0.1 ID sestavení: 1b3956717a60d6ac35b133d7b0a0f5eb55e9155]
It seems that it has been modified since Version 7.2.0 or later.