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.
Dear Jerzy Moruś, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug