Indirect cell reference (possibly) does not work in COUNTIFS and SUMIFS.
LibreOffice Version: 184.108.40.206, Linux Mint 20.3, Kernel 5.15.0-48-generic
(I also get the same error in “AndrOpen Office 5.2.2” which is based on Openoffice 4.1.12)
For a long time I have been building a cell reference out of fixed text and the content of a cell
to link to the next entry in an event log. That log has grown beyond the original limits and in
anticipation of future growth, I tried to get similar indirection to work in COUNTIFS and SUMIFS.
It doesn’t work, or I have got something wrong.
In the EventLog, column A contains an incrementing sequence number, column B the event date.
The cell that locates the next entry is F2, containing
The indirection is in E2,
As stated, this works perfectly. However, in a Summary sheet I count (and sum) all eventlog entries that match the criteria in A28 and Constants.$B$1.
gives me Err:502. Attempting to trace the invalid argument doesn’t really help, arrows point to
cells A28 and $F$2, with a single further arrow pointing to a small diamond blob that does nothing…
however, specifying a fixed range,
works perfectly, but does not protect me from future growth…
I have even tried specifying the sheet label in every cell reference,
SUMIFS fails in exactly the same way.
does not, Err:502.
Remember, replacing the indirection with an absolute cell reference works as expected.
Is it me, do I have a syntax error somewhere?
Could it be an intentional design feature for which I cannot find documentation?
Does indirection work anywhere other than in a hyperlink?
Impossibly, could I have tripped over a bug?
[erAck: edited to format formula expressions as code for readability and correctness, see This is the guide - How to use the Ask site? - #6 by erAck ]