Issue 48761 - cell range syntax A1:B2 does not support formulas for the cell reference
Summary: cell range syntax A1:B2 does not support formulas for the cell reference
Status: CLOSED DUPLICATE of issue 4904
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOo 2.0 Beta
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2005-05-05 11:05 UTC by sinewalker
Modified: 2005-05-09 17:45 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
Example Calc sheet with formula showing range parse error. (11.75 KB, application/vnd.sun.xml.calc)
2005-05-05 11:07 UTC, sinewalker
no flags Details
Example Excel sheet with similar range syntax, working (16.00 KB, application/vnd.ms-excel)
2005-05-05 11:08 UTC, sinewalker
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description sinewalker 2005-05-05 11:05:14 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))))
Comment 1 sinewalker 2005-05-05 11:07:36 UTC
Created attachment 25815 [details]
Example Calc sheet with formula showing range parse error.
Comment 2 sinewalker 2005-05-05 11:08:33 UTC
Created attachment 25816 [details]
Example Excel sheet with similar range syntax, working
Comment 3 sinewalker 2005-05-05 11:14:48 UTC
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.
Comment 4 daniel.rentz 2005-05-09 17:45:09 UTC
double

*** This issue has been marked as a duplicate of 4904 ***
Comment 5 daniel.rentz 2005-05-09 17:45:27 UTC
double -> closed