Bug 145048 - Calc's CELL function: missing functionality
Summary: Calc's CELL function: missing functionality
Status: RESOLVED DUPLICATE of bug 106151
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.2.1.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-10-10 22:03 UTC by hardcoder
Modified: 2021-10-11 22:02 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Example workbooks using CELL() and their screenshots, zip-packed. (130.68 KB, application/x-zip-compressed)
2021-10-10 23:00 UTC, hardcoder
Details

Note You need to log in before you can comment on or make changes to this bug.
Description hardcoder 2021-10-10 22:03:29 UTC
Description:
MS Excel's CELL formula function has had a stable behavior for more than 10 years, and WPS Office also follows this standard. As LibreOffice came later than Excel and supports .xls and .xlsx files, it should have a compatible implementation of this function.

When a second parameter (a cell reference) is not given to the CELL function, it refers to the last edited cell (when a cell has been edited, triggering a recalc), or to the selected cell (ignoring range selection). In Calc, the function instead refers to the cell the formula is in (not quite useful).

This feature is apparently not well-known, but it's a game-changer because it allows interactivity in a spreadsheet without any macros. If used together with iteration, it can even be used to create controls like buttons, check boxes, pickers and spinners/updowns, without need for form controls.

Pardon my poor English.

Steps to Reproduce:
1. In a new/empty spreadsheet, go to some cell and give it the formula: =CELL("address")
Let's say you put that on cell A1. The cell will show an address string like $A$1.
2. Select a random cell and press F9 (recalc). The contents of cell A1 should reflect the address of the last selected cell.
3. Edit some other cell and press Enter. The content of cell A1 should reflect the address of the last edited cell.
4. The CELL function also accepts other values for the first parameter (returning other data about the same cell), such as "col" (returns the column index) and "row" (row index).

Actual Results:
The formula result is just about the cell it is in, regardless of selection or what cell was edited last. For example if in cell A1 you put =CELL("address"), its result will constantly be $A$1. If it were =CELL("col") or =CELL("row"), the return would be 1 for A1.

Expected Results:
The cell value should be an address or other attribute of the cell that was last edited by the user, or (if recalc was invoked but no cell was changed) the selected cell.
The behavior can be checked at least in Excel for Windows, Excel for Android, Excel Online and WPS Office Free.


Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
Version: 7.2.1.2 (x64) / LibreOffice Community
Build ID: 87b77fad49947c1441b67c559c339af8f3517e22
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
Comment 1 hardcoder 2021-10-10 23:00:23 UTC
Created attachment 175627 [details]
Example workbooks using CELL() and their screenshots, zip-packed.

I'm uploading three .xlsx workbooks using the CELL function, in a .zip file:

2021-10-10-Highlighter.xlsx: A simple sheet that has a table with some numbers. You can highlight all occurrences of a number in the table by selecting a cell with the number and hitting F9 (recalculate). This was achieved through conditional formatting.

2020-03-09-Cell-controls.xlsx: A sheet showcasing a few in-cell controls: a boolean switcher, a vowel switcher and a spin button. They are reusable within a sheet, thanks to defined names.

2021-03-31-Game03.xlsx: A sokoban-like game (that's right, a spreadsheet game without macros). There are more complex games that can also be made, but I read that I should "make the [attached] document as minimal as possible." I can upload another game on request.

These last two workbooks also depend on iterative calculation; as Calc's iteration is replete with bugs, it will certainly take some time before they work well in LibreOffice. But they work fine in Excel for Windows and WPS Office Free. I also included .png screenshots of each workbook.
Comment 2 m.a.riosv 2021-10-11 08:50:00 UTC

*** This bug has been marked as a duplicate of bug 106151 ***
Comment 3 hardcoder 2021-10-11 22:02:53 UTC
(In reply to m.a.riosv from comment #2)
> 
> *** This bug has been marked as a duplicate of bug 106151 ***

Will you do something about bug 106151?