Re: [libreoffice-users] [CALC] row_only and column_only cell references

> On 10/12/2013 07:55 AM, Ady wrote:
> > Hello,
> >
> > I've been having problems with certain type of *references* in Calc.
> >
> > For example, the formula "sum(1:2)" works correctly in other
> > spreadsheet software, but it errors in Calc.
>
> I was not aware that you could do that in any product. It is not 
> supported in Calc of which I am aware. Villeroy has a messy solution 
> (see post 6). Well, you probably should read all the posts...
>
> http://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=9527
>
> So, the ony soltions I have seen based on a quick search is to either 
> define a range, or to use offset.
>
> > The most problematic cases are related to functions such as
> > "indirect()", "address()" and the like. For example, the following
> > formula "should" tell whether a number in "A1" is prime or not:
> >
> > =SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT("1:"&INT(A1^0.5))))=0))=1
> >
> > but Calc doesn't seem to accept the type of reference
> > ("ini_row:fin_row"). Instead I have to add a "column" to the
> > reference, as in:
> >
> > =SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT("a1:a"&INT(A1^0.5))))=0))=1
> >
> > to make it work in Calc.
> >
> > There are several settings that affect the way Calc parses and shows
> > some type of cell references, so I wonder whether there is some
> > setting that I should change for Calc to be able to accept this type
> > of references ("ini_row:fin_row"), or whether this is some kind of
> > bug or lack of a feature.
> >
> > A similar issue happens with column-only references, such as
> > "sum(A:B)".
> >
> > To be clear, I'm not saying that row-only (or column-only) references
> > are better in any way. I'm just trying to make them work as in (a.k.a
> > "compatible with") other spreadsheet software.
> >
> > If there is a better place (mailing list, irc, forum,...) to ask this
> > question, please let me know.
> >
> > TIA,
> > Ady.
>
> -- 
> Andrew Pitonyak

Quoting from "CG4107-FormulasAndFunctions.pdf" :
"Calc can not reference a whole column of unspecified length using 
A:A or a whole row using 1:1 
which you might be familiar with in other spreadsheet programs."

This piece of info was not so easy to find. Since other spreadsheet 
software (in fact, more than one) indeed supports "A:A: and "1:1" 
references, I thought that either Calc would support it too, or at 
least would be easier to find that it doesn't.

For example, using Calc to open some xls file with such type of 
references would end up with errors. The xls file itself would open, 
but there would be no hint about the reason for the "#ref" or "#name" 
errors (among the possible errors that this type of cell reference 
would end up with).

Regarding a workaround, once the user finds out that the problem is 
in the type of reference (which is not always evident), then by 
adding some column (or some row) to the reference would probably 
avoid the error. Other methods (e.g offset, range names) would be 
more close to the original range (complete row / column). This 
workaround is of course not always available, specially when 
receiving a spreadsheet file from someone else (originally prepared 
and saved with other programs).

I am still wondering if some setting in Calc would (or should) allow 
for this type of cell reference. For example, by using "Excel A1" 
instead of "Calc A1" in Calc -> Tools -> Options -> LibreOffice Calc 
-> Formula -> Formula syntax.

There are many different settings that might influence the accepted 
behavior, and I really don't know / understand the effect of each one 
(I didn't find any explanation of what each setting really means), so 
instead of "playing" with each possible combination, I decided to ask 
here in the ML (and not all of those settings are located in the same 
place).

If this type of cell reference is _completely_ unsupported, perhaps 
it should be mentioned in the help files (?). I would agree that 
referencing complete columns or complete rows should be rarely used, 
and discouraged, but perhaps supporting a formula that already uses 
it (specially when using or receiving files originally saved with 
other file formats) is not such a crazy idea.

So, is there any combination of settings that would help in this type 
of cases?


[off-topic]
BTW, if I may... Having separated mailing lists according to the 
program (Writer / Calc / Impress / ...) might be more adequate for 
users, specially since other methods of communication with 
LibreOffice / TDF are not really 
effectively working.
[/off-topic]

TIA,
Ady.

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted