Calc Selections of Entire Rows and Columns

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Calc Selections of Entire Rows and Columns

Dennis E. Hamilton-2
The specification of entire rows and columns in cell range addresses is covered in the ODF 1.2 specification.  Part 1 provides some detail in section 9.2.4 Column and Row Range Addresses.  The OpenFormula specification (ODF 1.2 Part 2) provides syntax for it in section 5.8 References.

This is what is communicated in the ODF for a Spreadsheet document.  This is not necessarily the format shown for a cell formula in the UI, or as entered in the formula-entry field.

It would be good to see what happen with these on entry (and where any intersection rules apply) and also when encountered in the document files.  

EXAMPLE

I made an Excel 2016 .xlsx file that computers Fibonacci numbers in column A.  (A1 is 1, A2 is 1, A3 = A1 + A2, further values in Column A by filling down from A3 to row 26 (arbitrary choice).  I set B1 = A:A and then did a fill down to B26.  Each of the cell still had the formula =A:A and the value was that of the adjacent A cell.

I was able to save this as a *.ods file.  When I reopened it in Excel, it had preserved the same formulas.

When I opened the .xlsx in LibreOffice Calc, it also preserved the =A:A formulas in the opened sheet.  Likewise, the .ods =A:A files were preserved.

AOO Calc rewrote each =A:A from Excel as =A$1:A$1048576.  AOO Calc preserved the =A:A formulas in the .ods but evaluated them as #NAME?

INTEROPERABILITY ISSUES

An interesting problem this creates in interchange is the fact that all previous versions of AOO will fail if the =A:A and other cases of full column/row selections are now produced in the .ods document.  That will also be the case with documents from LibreOffice since =A:A and its cousins have been working.

LibreOffice avoids this down-level interoperability problem by accepting =A:A in Excel and .ods, but when the .ods file is saved, the =A:A cell formulas are rewritten as =A$1:A$1048576.  I checked, that is what is written in the OpenFormula values.

Microsoft Excel 2016, when it sees these in an .ods file, it presents =A:A in the formula window.  And so does LibreOffice on rereading even the one it appears to writes.  Classy.

So, when =A:A and related cell ranges are supported, they should be written out with the max ranges (i.e., A$1:A$1048576) in the spreadsheet file, and those can be presented as =A:A to users and accepted from users, etc.

Verrrry interessssting.

 - Dennis


> -----Original Message-----
> From: Damjan Jovanovic [mailto:[hidden email]]
> Sent: Thursday, December 10, 2015 09:07
> To: Apache OO <[hidden email]>
> Cc: [hidden email]
> Subject: Re: Introducing my self and my goals.
>
[ ... ]
> * Calc doesn't provide whole row/column references like C:C or 5:5 while
> Excel does, which are helpful to use in implicit intersection, among
> others
> (eg. in cell E34, you don't need to use D34 to refer to the cell just to
> the left, you can just type D:D and it will automatically use the
> current
> row).
[ ... ]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: Calc Selections of Entire Rows and Columns

Dennis E. Hamilton-3
The TL;DR: For full row and column selections in formulas, the trick is to arrange to accept something like A:A and to recognize the explicit OO.o idiom (i.e., A$1:A$1048576) and show it as A:A.  The trick is to always write, in the OpenFormula, A$1:A$1048576, regardless of the form it was read/input, and always display as A:A in the presented formula.  That is, always write the idiom but recognize both it and the general form (and all variations of course) as the general form.  

This will work with ODF Spreadsheets across AOO, LibreOffice, and ODF-1.2-supporting Excel versions.  It also works down-level to older versions of AOO, LibO, OpenOffice.org, etc., wherever they are still in use.

PS: I looked for a Bugzilla issue on this enhancement but couldn't find it.  I will look again.

PPS: It might work just to say "=A" instead of "A:A" in the case of a single column.  For rows it is trickier and might need the [.2] notation.  (In the UI, there is ambiguity with numerical values and named variables otherwise.)  Note that the way =A:A is recorded using OpenFormula in the file itself is with <table:table-cell> element attribute table:formula="of:=[.A$1:.A$1048576]", using the OO.o-specific idiom.  When Excel produces ODF spreadsheet documents, it records the user's =A:A using the general form table:formula="of:=[.A:.A]", and it accepts the explicit form too (a little nod to the OpenOffice.org idiom in the spirit of interoperability).

 - Dennis

> -----Original Message-----
> From: Dennis E. Hamilton [mailto:[hidden email]]
> Sent: Saturday, December 12, 2015 19:50
> To: [hidden email]
> Cc: [hidden email]; '#PATHANGI JANARDHANAN JATINSHRAVAN#'
> <[hidden email]>
> Subject: Calc Selections of Entire Rows and Columns
>
> The specification of entire rows and columns in cell range addresses is
> covered in the ODF 1.2 specification.  Part 1 provides some detail in
> section 9.2.4 Column and Row Range Addresses.  The OpenFormula
> specification (ODF 1.2 Part 2) provides syntax for it in section 5.8
> References.
>
> This is what is communicated in the ODF for a Spreadsheet document.
> This is not necessarily the format shown for a cell formula in the UI,
> or as entered in the formula-entry field.
>
> It would be good to see what happen with these on entry (and where any
> intersection rules apply) and also when encountered in the document
> files.
>
> EXAMPLE
>
> I made an Excel 2016 .xlsx file that computers Fibonacci numbers in
> column A.  (A1 is 1, A2 is 1, A3 = A1 + A2, further values in Column A
> by filling down from A3 to row 26 (arbitrary choice).  I set B1 = A:A
> and then did a fill down to B26.  Each of the cell still had the formula
> =A:A and the value was that of the adjacent A cell.
>
> I was able to save this as a *.ods file.  When I reopened it in Excel,
> it had preserved the same formulas.
>
> When I opened the .xlsx in LibreOffice Calc, it also preserved the =A:A
> formulas in the opened sheet.  Likewise, the .ods =A:A files were
> preserved.
>
> AOO Calc rewrote each =A:A from Excel as =A$1:A$1048576.  AOO Calc
> preserved the =A:A formulas in the .ods but evaluated them as #NAME?
>
> INTEROPERABILITY ISSUES
>
> An interesting problem this creates in interchange is the fact that all
> previous versions of AOO will fail if the =A:A and other cases of full
> column/row selections are now produced in the .ods document.  That will
> also be the case with documents from LibreOffice since =A:A and its
> cousins have been working.
>
> LibreOffice avoids this down-level interoperability problem by accepting
> =A:A in Excel and .ods, but when the .ods file is saved, the =A:A cell
> formulas are rewritten as =A$1:A$1048576.  I checked, that is what is
> written in the OpenFormula values.
>
> Microsoft Excel 2016, when it sees these in an .ods file, it presents
> =A:A in the formula window.  And so does LibreOffice on rereading even
> the one it appears to writes.  Classy.
>
> So, when =A:A and related cell ranges are supported, they should be
> written out with the max ranges (i.e., A$1:A$1048576) in the spreadsheet
> file, and those can be presented as =A:A to users and accepted from
> users, etc.
>
> Verrrry interessssting.
>
>  - Dennis
>
>
> > -----Original Message-----
> > From: Damjan Jovanovic [mailto:[hidden email]]
> > Sent: Thursday, December 10, 2015 09:07
> > To: Apache OO <[hidden email]>
> > Cc: [hidden email]
> > Subject: Re: Introducing my self and my goals.
> >
> [ ... ]
> > * Calc doesn't provide whole row/column references like C:C or 5:5
> while
> > Excel does, which are helpful to use in implicit intersection, among
> > others
> > (eg. in cell E34, you don't need to use D34 to refer to the cell just
> to
> > the left, you can just type D:D and it will automatically use the
> > current
> > row).
> [ ... ]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc Selections of Entire Rows and Columns

Damjan Jovanovic
On Sun, Dec 13, 2015 at 6:12 PM, Dennis E. Hamilton <[hidden email]>
wrote:

> The TL;DR: For full row and column selections in formulas, the trick is to
> arrange to accept something like A:A and to recognize the explicit OO.o
> idiom (i.e., A$1:A$1048576) and show it as A:A.  The trick is to always
> write, in the OpenFormula, A$1:A$1048576, regardless of the form it was
> read/input, and always display as A:A in the presented formula.  That is,
> always write the idiom but recognize both it and the general form (and all
> variations of course) as the general form.
>
> This will work with ODF Spreadsheets across AOO, LibreOffice, and
> ODF-1.2-supporting Excel versions.  It also works down-level to older
> versions of AOO, LibO, OpenOffice.org, etc., wherever they are still in use.
>
> PS: I looked for a Bugzilla issue on this enhancement but couldn't find
> it.  I will look again.
>

The issue was duplicated countless times, but the earliest report seems to
be https://bz.apache.org/ooo/show_bug.cgi?id=20495


> PPS: It might work just to say "=A" instead of "A:A" in the case of a
> single column.  For rows it is trickier and might need the [.2] notation.
> (In the UI, there is ambiguity with numerical values and named variables
> otherwise.)  Note that the way =A:A is recorded using OpenFormula in the
> file itself is with <table:table-cell> element attribute
> table:formula="of:=[.A$1:.A$1048576]", using the OO.o-specific idiom.  When
> Excel produces ODF spreadsheet documents, it records the user's =A:A using
> the general form table:formula="of:=[.A:.A]", and it accepts the explicit
> form too (a little nod to the OpenOffice.org idiom in the spirit of
> interoperability).
>
>
"=A" is also problematic if "A" is a named range.

The patch Jatin sent me to review already allows entering A:A and 1:1, and
writes A:A as [.A$1:.A$1048576], but re-reads it as A$1:A$1048576. It can
read [.A:.A] but also converts it to A$1:A$1048576.


>  - Dennis
>
>
Damjan


> > -----Original Message-----
> > From: Dennis E. Hamilton [mailto:[hidden email]]
> > Sent: Saturday, December 12, 2015 19:50
> > To: [hidden email]
> > Cc: [hidden email]; '#PATHANGI JANARDHANAN JATINSHRAVAN#'
> > <[hidden email]>
> > Subject: Calc Selections of Entire Rows and Columns
> >
> > The specification of entire rows and columns in cell range addresses is
> > covered in the ODF 1.2 specification.  Part 1 provides some detail in
> > section 9.2.4 Column and Row Range Addresses.  The OpenFormula
> > specification (ODF 1.2 Part 2) provides syntax for it in section 5.8
> > References.
> >
> > This is what is communicated in the ODF for a Spreadsheet document.
> > This is not necessarily the format shown for a cell formula in the UI,
> > or as entered in the formula-entry field.
> >
> > It would be good to see what happen with these on entry (and where any
> > intersection rules apply) and also when encountered in the document
> > files.
> >
> > EXAMPLE
> >
> > I made an Excel 2016 .xlsx file that computers Fibonacci numbers in
> > column A.  (A1 is 1, A2 is 1, A3 = A1 + A2, further values in Column A
> > by filling down from A3 to row 26 (arbitrary choice).  I set B1 = A:A
> > and then did a fill down to B26.  Each of the cell still had the formula
> > =A:A and the value was that of the adjacent A cell.
> >
> > I was able to save this as a *.ods file.  When I reopened it in Excel,
> > it had preserved the same formulas.
> >
> > When I opened the .xlsx in LibreOffice Calc, it also preserved the =A:A
> > formulas in the opened sheet.  Likewise, the .ods =A:A files were
> > preserved.
> >
> > AOO Calc rewrote each =A:A from Excel as =A$1:A$1048576.  AOO Calc
> > preserved the =A:A formulas in the .ods but evaluated them as #NAME?
> >
> > INTEROPERABILITY ISSUES
> >
> > An interesting problem this creates in interchange is the fact that all
> > previous versions of AOO will fail if the =A:A and other cases of full
> > column/row selections are now produced in the .ods document.  That will
> > also be the case with documents from LibreOffice since =A:A and its
> > cousins have been working.
> >
> > LibreOffice avoids this down-level interoperability problem by accepting
> > =A:A in Excel and .ods, but when the .ods file is saved, the =A:A cell
> > formulas are rewritten as =A$1:A$1048576.  I checked, that is what is
> > written in the OpenFormula values.
> >
> > Microsoft Excel 2016, when it sees these in an .ods file, it presents
> > =A:A in the formula window.  And so does LibreOffice on rereading even
> > the one it appears to writes.  Classy.
> >
> > So, when =A:A and related cell ranges are supported, they should be
> > written out with the max ranges (i.e., A$1:A$1048576) in the spreadsheet
> > file, and those can be presented as =A:A to users and accepted from
> > users, etc.
> >
> > Verrrry interessssting.
> >
> >  - Dennis
> >
> >
> > > -----Original Message-----
> > > From: Damjan Jovanovic [mailto:[hidden email]]
> > > Sent: Thursday, December 10, 2015 09:07
> > > To: Apache OO <[hidden email]>
> > > Cc: [hidden email]
> > > Subject: Re: Introducing my self and my goals.
> > >
> > [ ... ]
> > > * Calc doesn't provide whole row/column references like C:C or 5:5
> > while
> > > Excel does, which are helpful to use in implicit intersection, among
> > > others
> > > (eg. in cell E34, you don't need to use D34 to refer to the cell just
> > to
> > > the left, you can just type D:D and it will automatically use the
> > > current
> > > row).
> > [ ... ]
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [hidden email]
> > For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Calc Selections of Entire Rows and Columns

Andreas Säger-2
In reply to this post by Dennis E. Hamilton-3
Am 13.12.2015 um 17:12 schrieb Dennis E. Hamilton:
> The TL;DR: For full row and column selections in formulas, the trick is to arrange to accept something like A:A and to recognize the explicit OO.o idiom (i.e., A$1:A$1048576) and show it as A:A.  The trick is to always write, in the OpenFormula, A$1:A$1048576, regardless of the form it was read/input, and always display as A:A in the presented formula.  That is, always write the idiom but recognize both it and the general form (and all variations of course) as the general form.  
>

There is a difference between A:A and A$1:A$1048576 in Excel and
Gnumeric: When you delete rows, A:A remains A:A whereas A$1:A$1048576
shrinks. Any reference to the last cell A$1048576 moves up but any A:A
reference remains the same regardless how many and how often you delete
cells. In the age of spreadsheet databases this may be a problem when
you count on this behaviour.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: Calc Selections of Entire Rows and Columns

Dennis E. Hamilton-2
> -----Original Message-----
> From: Andreas Säger [mailto:[hidden email]]
> Sent: Sunday, December 13, 2015 13:15
> To: [hidden email]
> Subject: Re: Calc Selections of Entire Rows and Columns
>
> Am 13.12.2015 um 17:12 schrieb Dennis E. Hamilton:
> > The TL;DR: For full row and column selections in formulas, the trick
> is to arrange to accept something like A:A and to recognize the explicit
> OO.o idiom (i.e., A$1:A$1048576) and show it as A:A.  The trick is to
> always write, in the OpenFormula, A$1:A$1048576, regardless of the form
> it was read/input, and always display as A:A in the presented formula.
> That is, always write the idiom but recognize both it and the general
> form (and all variations of course) as the general form.
> >
>
> There is a difference between A:A and A$1:A$1048576 in Excel and
> Gnumeric: When you delete rows, A:A remains A:A whereas A$1:A$1048576
> shrinks. Any reference to the last cell A$1048576 moves up but any A:A
> reference remains the same regardless how many and how often you delete
> cells. In the age of spreadsheet databases this may be a problem when
> you count on this behaviour.
[orcmid]
Thanks Andreas,

Do you recommend the A:A unchanging behavior or the A$1:A$1048576 shrinking behavior?  Do folks depend on the shrinking behavior of the idiom?

Won't the unchanging A:A type of behavior be more complicated to achieve?  It means having true full column and full row recognized and displayed, including input of the idiom form files, but using the idiom on output of ODF 1.2 open formulas to preserve interoperability with older implementations.

PS: I notice if I insert rows, the range doesn't change.  Only if I delete rows does the range shrink.  So if I add rows and then take them out, the range decreases.  Not exactly marvelous.  

>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: Calc Selections of Entire Rows and Columns

Dennis E. Hamilton
In reply to this post by Damjan Jovanovic
+1 Good enough.

Display the general form instead of the OO.o idiom would be better, in terms of what is probably a more-desirable form recognized as such in a wider community, and what would be good to encourage.  

The key thing is to no longer fail on receiving A:A and its counterparts.

 - Dennis

> -----Original Message-----
> From: Damjan Jovanovic [mailto:[hidden email]]
> Sent: Sunday, December 13, 2015 08:57
> To: Dennis Hamilton <[hidden email]>
> Cc: Apache OO <[hidden email]>; #PATHANGI JANARDHANAN
> JATINSHRAVAN# <[hidden email]>
> Subject: Re: Calc Selections of Entire Rows and Columns
>
> On Sun, Dec 13, 2015 at 6:12 PM, Dennis E. Hamilton <[hidden email]>
> wrote:
[ ... ]
> > PS: I looked for a Bugzilla issue on this enhancement but couldn't
> find
> > it.  I will look again.
> >
>
> The issue was duplicated countless times, but the earliest report seems
> to
> be https://bz.apache.org/ooo/show_bug.cgi?id=20495
>
[ ... ]
> "=A" is also problematic if "A" is a named range.
>
> The patch Jatin sent me to review already allows entering A:A and 1:1,
> and
> writes A:A as [.A$1:.A$1048576], but re-reads it as A$1:A$1048576. It
> can
> read [.A:.A] but also converts it to A$1:A$1048576.
>
[ ... ]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: Calc Selections of Entire Rows and Columns

Dennis E. Hamilton
In reply to this post by Dennis E. Hamilton-2
I took another look at the A$1:A$1048576 shrinkage by deletion of rows having such cells and also having them in surrounding rows.

If an OpenOffice.org descendant, including AOO, saves a result of such shrinkage, it stays shrunk.  That is, AOO, LibreOffice, and Excel2016 will accept the shrunken range as exact and *not* treat it as A:A.

If enough insertions are made to such a file to get to A$1:A$1048576 in either LibreOffice 5.0 or Excel 2016, the range is displayed as A:A again.  AOO simply maxes out at the idiom value.

If LibreOffice opens a .ods having A:A produced by Excel, and deletes rows as above, the range remains A:A.  Ditto if Excel does that with a .ods produced by LibreOffice.

There appears to be a clear pattern on how this works for interoperability among those ODF-supporting products that recognize A:A in their UI and in spreadsheets that they open where there is either =[.A:.A] or =[.A$1:.A$1048576] being taken as A:A on input of the OpenFormula in the .ods.

There are more test cases to nail down the apparent principle.  The pattern seems clear enough to see if that is confirmed with other tests.

 - Dennis


> -----Original Message-----
> From: Dennis E. Hamilton [mailto:[hidden email]]
> Sent: Sunday, December 13, 2015 13:54
> To: [hidden email]
> Subject: RE: Calc Selections of Entire Rows and Columns
>
> > -----Original Message-----
> > From: Andreas Säger [mailto:[hidden email]]
> > Sent: Sunday, December 13, 2015 13:15
> > To: [hidden email]
> > Subject: Re: Calc Selections of Entire Rows and Columns
> >
> > Am 13.12.2015 um 17:12 schrieb Dennis E. Hamilton:
> > > The TL;DR: For full row and column selections in formulas, the trick
> > is to arrange to accept something like A:A and to recognize the
> explicit
> > OO.o idiom (i.e., A$1:A$1048576) and show it as A:A.  The trick is to
> > always write, in the OpenFormula, A$1:A$1048576, regardless of the
> form
> > it was read/input, and always display as A:A in the presented formula.
> > That is, always write the idiom but recognize both it and the general
> > form (and all variations of course) as the general form.
> > >
> >
> > There is a difference between A:A and A$1:A$1048576 in Excel and
> > Gnumeric: When you delete rows, A:A remains A:A whereas A$1:A$1048576
> > shrinks. Any reference to the last cell A$1048576 moves up but any A:A
> > reference remains the same regardless how many and how often you
> delete
> > cells. In the age of spreadsheet databases this may be a problem when
> > you count on this behaviour.
> [orcmid]
> Thanks Andreas,
>
> Do you recommend the A:A unchanging behavior or the A$1:A$1048576
> shrinking behavior?  Do folks depend on the shrinking behavior of the
> idiom?
>
> Won't the unchanging A:A type of behavior be more complicated to
> achieve?  It means having true full column and full row recognized and
> displayed, including input of the idiom form files, but using the idiom
> on output of ODF 1.2 open formulas to preserve interoperability with
> older implementations.
>
> PS: I notice if I insert rows, the range doesn't change.  Only if I
> delete rows does the range shrink.  So if I add rows and then take them
> out, the range decreases.  Not exactly marvelous.
>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [hidden email]
> > For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: Calc Selections of Entire Rows and Columns

Dennis E. Hamilton-2
In reply to this post by Damjan Jovanovic
Is there a Bugzilla issue with Jatin's patch?

> -----Original Message-----
> From: Damjan Jovanovic [mailto:[hidden email]]
> Sent: Sunday, December 13, 2015 08:57
> To: Dennis Hamilton <[hidden email]>
> Cc: Apache OO <[hidden email]>; #PATHANGI JANARDHANAN
> JATINSHRAVAN# <[hidden email]>
> Subject: Re: Calc Selections of Entire Rows and Columns
[ ... ]
> "=A" is also problematic if "A" is a named range.
>
> The patch Jatin sent me to review already allows entering A:A and 1:1,
> and
> writes A:A as [.A$1:.A$1048576], but re-reads it as A$1:A$1048576. It
> can
> read [.A:.A] but also converts it to A$1:A$1048576.
[ ... ]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc Selections of Entire Rows and Columns

#PATHANGI JANARDHANAN JATINSHRAVAN#
Hi Dennis,
         I have filed one here: https://bz.apache.org/ooo/show_bug.cgi?id=126734 with the patch available in that issue

Thanks
Jatin

________________________________________
From: Dennis E. Hamilton <[hidden email]>
Sent: Monday, December 14, 2015 09:13 AM
To: [hidden email]
Cc: #PATHANGI JANARDHANAN JATINSHRAVAN#
Subject: RE: Calc Selections of Entire Rows and Columns

Is there a Bugzilla issue with Jatin's patch?

> -----Original Message-----
> From: Damjan Jovanovic [mailto:[hidden email]]
> Sent: Sunday, December 13, 2015 08:57
> To: Dennis Hamilton <[hidden email]>
> Cc: Apache OO <[hidden email]>; #PATHANGI JANARDHANAN
> JATINSHRAVAN# <[hidden email]>
> Subject: Re: Calc Selections of Entire Rows and Columns
[ ... ]
> "=A" is also problematic if "A" is a named range.
>
> The patch Jatin sent me to review already allows entering A:A and 1:1,
> and
> writes A:A as [.A$1:.A$1048576], but re-reads it as A$1:A$1048576. It
> can
> read [.A:.A] but also converts it to A$1:A$1048576.
[ ... ]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc Selections of Entire Rows and Columns

Joost Andrae-2
In reply to this post by Dennis E. Hamilton-2
Hi Jatin,

it would be great to have that functionality in Calc but did you
consider to support this within the Excel import filter as it is an
Excel functionality ?

Alternatively a user can use this:
When using the magic type range names (adding a column header you can
parse over the whole column that has this header) you can reference the
whole input area within calc...

Example: Add eg. the sting "banana" as a column header somewhere within
a sheet and add some numerical data downside of it then at a place
somewhere else within the sheet you can use it like =sum(banana)

Differently to Excel you can add data whereelse you want within that
range (at the beginning, within that range (adding rows within that
area) and adding columns at the end of that range)

just my 2 cents....


Kind regards, Joost


Am 14.12.2015 um 04:23 schrieb #PATHANGI JANARDHANAN JATINSHRAVAN#:
> Hi Dennis,
>           I have filed one here: https://bz.apache.org/ooo/show_bug.cgi?id=126734 with the patch available in that issue
>
> Thanks
> Jatin
>



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc Selections of Entire Rows and Columns

#PATHANGI JANARDHANAN JATINSHRAVAN#
Hi Andre,
        Yes, I can take a look at implementing that, but can we make that a separate issue and file another ticket for that with a new patch? That would probably be better suited for a different patch to this one. That’s what I feel.

Thanks
Jatin




On 12/15/15, 4:10 AM, "Joost Andrae" <[hidden email]> wrote:

>of

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

RE: Calc Selections of Entire Rows and Columns

Dennis E. Hamilton-2
+1

Good idea.  The current issues are is around interoperability and improvement of OpenFormula implementation.  The other is about usability and UI behavior.  No need to tailgate one onto the other.

> -----Original Message-----
> From: #PATHANGI JANARDHANAN JATINSHRAVAN#
> [mailto:[hidden email]]
> Sent: Tuesday, December 15, 2015 03:23
> To: [hidden email]
> Subject: Re: Calc Selections of Entire Rows and Columns
>
> Hi Andre,
> Yes, I can take a look at implementing that, but can we make that a
> separate issue and file another ticket for that with a new patch? That
> would probably be better suited for a different patch to this one.
> That’s what I feel.
>
> Thanks
> Jatin
>
>
>
>
> On 12/15/15, 4:10 AM, "Joost Andrae" <[hidden email]> wrote:
>
> >of
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc Selections of Entire Rows and Columns

#PATHANGI JANARDHANAN JATINSHRAVAN#
By the way, if I want to display A:A as ‘A:A’ instead of 'A$1:A$1048576’ when opening a saved spreadsheet file, would ‘sc/source/core/tool/compiler.cxx' be the right file to touch? This is where I could find the most relevant functions.




On 12/16/15, 12:36 AM, "Dennis E. Hamilton" <[hidden email]> wrote:

>+1
>
>Good idea.  The current issues are is around interoperability and improvement of OpenFormula implementation.  The other is about usability and UI behavior.  No need to tailgate one onto the other.
>
>> -----Original Message-----
>> From: #PATHANGI JANARDHANAN JATINSHRAVAN#
>> [mailto:[hidden email]]
>> Sent: Tuesday, December 15, 2015 03:23
>> To: [hidden email]
>> Subject: Re: Calc Selections of Entire Rows and Columns
>>
>> Hi Andre,
>> Yes, I can take a look at implementing that, but can we make that a
>> separate issue and file another ticket for that with a new patch? That
>> would probably be better suited for a different patch to this one.
>> That’s what I feel.
>>
>> Thanks
>> Jatin
>>
>>
>>
>>
>> On 12/15/15, 4:10 AM, "Joost Andrae" <[hidden email]> wrote:
>>
>> >of
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [hidden email]
>For additional commands, e-mail: [hidden email]
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]