https://bugs.documentfoundation.org/show_bug.cgi?id=145048
Bug ID: 145048
Summary: Calc's CELL function: missing functionality
Product: LibreOffice
Version: 7.2.1.2 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
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
--
You are receiving this mail because:
You are the assignee for the bug.
