Bug 161399 - Support Spilled range reference (# operator) in Calc
Summary: Support Spilled range reference (# operator) in Calc
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL: https://support.microsoft.com/en-us/o...
Whiteboard:
Keywords:
Depends on: 127808
Blocks: Excel-Functions
  Show dependency tree / graph
 
Reported: 2024-06-03 16:18 UTC by Gabor Kelemen (allotropia)
Modified: 2024-06-25 18:27 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
Example file from Exceljet (18.15 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2024-06-03 16:18 UTC, Gabor Kelemen (allotropia) 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen (allotropia) 2024-06-03 16:18:33 UTC 
Created attachment 194521 [details]
Example file from Exceljet

Add support to the "spilled range operator" in Calc functions.

1. Open attached file, taken from https://exceljet.net/formulas/dynamic-summary-count
2. Notice the COUNTIF function ( =COUNTIF(data[];_xlfn.anchorarray(E5)) ) in Sheet1:F5 does not work: it refers to the spilled range starting in E5

Version: 24.8.0.0.alpha1+ (X86_64) / LibreOffice Community
Build ID: d804d6aff49054bad1719ec3c2d136b545bbc7e7
CPU threads: 16; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: hu-HU (hu_HU.UTF-8); UI: en-US
Calc: threaded

This is a missing feature related to dynamic arrays.

See also:
https://www.ablebits.com/office-addins-blog/excel-spill-range/
Comment 1 m_a_riosv 2024-06-03 18:52:29 UTC 
+1, If it is affordable, it is promising.
Comment 2 Regina Henschel 2024-06-25 18:27:33 UTC 
It might be not the only way to solve the problem. A table in Excel cannot be defined on a dynamic array so the # addition to a reference is needed in Excel. A solution is LibreOffice could also be to treat a "dynamic array" similar to a database range and use its name as reference.

However, the # feature will only become relevant when the "dynamic array" feature is implemented. I therefore add a dependency to bug 127808 here.