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:orc...@apache.org] > Sent: Sunday, December 13, 2015 13:54 > To: dev@openoffice.apache.org > Subject: RE: Calc Selections of Entire Rows and Columns > > > -----Original Message----- > > From: Andreas Säger [mailto:saege...@t-online.de] > > Sent: Sunday, December 13, 2015 13:15 > > To: dev@openoffice.apache.org > > 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: dev-unsubscr...@openoffice.apache.org > > For additional commands, e-mail: dev-h...@openoffice.apache.org > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org > For additional commands, e-mail: dev-h...@openoffice.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org For additional commands, e-mail: dev-h...@openoffice.apache.org
