Apache OpenOffice (AOO) Bugzilla – Issue 48761
cell range syntax A1:B2 does not support formulas for the cell reference
Last modified: 2005-05-09 17:45:27 UTC
When entering a cell range into calc for the SUM() function, it does not parse the colon syntax if I use a reference function such as INDIRECT(). Non-range formulas do work however. I first saw this in OOo 1.1.1 and recreated in OOo 1.9.m100 (downloaded today). The attached calc spreadsheet shows an example formula using INDIRECT(ADDRESS(CELL("ROW";A8);3)) to return a cell reference as both parts of the range description: SUM(INDIRECT(ADDRESS(CELL("ROW";A8);3)):INDIRECT(ADDRESS(CELL("ROW";A10);3))) which results in Err508 (missing parameter). However if I replace the colon with a semicolon, this will parse (although it only sums the 1st and 3rd cell, not all three). This syntax does work in M$-Excel (I am attempting to convert a sheet to calc). An example of the Excel formula is attached too. Although it uses different functions to achieve the same aim, it is the range syntax that is the issue. I was unsure if I should classify this as a DEFECT. It could also be treated as an ENHANCEMENT request: a new spreadsheet function that can take cell references as arguments and return a range would be a suitable fix for this. i.e. if this proposed new function was called RANGE(): SUM(RANGE(INDIRECT(ADDRESS(CELL("ROW";A8);3));INDIRECT(ADDRESS(CELL("ROW";A10);3))))
Created attachment 25815 [details] Example Calc sheet with formula showing range parse error.
Created attachment 25816 [details] Example Excel sheet with similar range syntax, working
Named ranges: I had thought to use these, but they aren't what I am after. The sheet I am trying to convert is using some arithmetic on the cell refernces to create dynamically changing ranges. That's why all the wierd functions are being used here. Sample formulas are at the bottom of both sheets. Though the don't work in my example and there is probably not enough context to understand, I felt that including them may explain my intentions better.
double *** This issue has been marked as a duplicate of 4904 ***
double -> closed