Jean Hollis Weber wrote:
I am thoroughly confused about the choices for naming ranges in Calc.
Can someone clarify for me the difference between Insert > Names >
Define and Data > Define Ranges? When should one or the other be used?
Are the results different in functionality?
Thanks! --Jean
A named reference (my prefered term) can be any formula expression. Try out:
VAT 0.175 [named constant]
myName "Jean Hollis Weber" [named text constant]
LastRow OFFSET($A$1;COUNT($A$1:$A$65536)) [named calculation]
SheetName =MID(cFilename;FIND("#$";cFilename)+2;LEN(cFilename)) [returns
the name of the sheet where the name is used]
It can be a named range, which can be any absolute or relative referece
to one or more ranges. Relative references are relative to the active
cell when difining the name.
You define "RightNeigbour" as B1 while A1 is the active input cell, then
this name will always refer to the next cell to the right of any cell
where you use this name.
You set extra flags for a named reference, so the respective name will
be visible in some list box of the advanced filter dialog (as a named
range of filter criteria) or the print ranges set up as named print ranges.
A database range is a completely different thing which is only in OOo
Calc. It always refers to one particular rectangle of cells by means of
an absolute reference $SheetName.$A$1:$X$99 with all the dollar signs.
Basically it remembers all the sophisticated settings how a
database-like list has been sorted, filtered, subtotaled and/or imported
from a registered data source.
Data>Refresh refreshes all those settings after data have been edited,
or after rows have been inserted or deleted.
It solves most of the problems people use to have with filters and sort
orders.
For instance it is impossible use more filtered range unless you define
your database-like lists as database ranges. After this step you can
even have more than one list on the same sheet.
You lose all the sophisticated extra options for your special sort order
after you used some other db-range elswhere. This does not happen with a
named database range.
A database range has 4 extra options.
The first one specifies if the range is supposed to have column labels
or not. This is only relevant for ranges that are not imported from data
source. Import ranges always come in with labels.
The other 3 options are only relevant with import ranges.
[Tutorial] Using registered datasources in Calc:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=75&t=18511
Insert>Names>Labels... is another flea circus of options being in there
simply because they are in Excel. A single-quoted name as in
SUM('Amount') refers to the column having "Amount" as column label.
For several reasons this feature is close to unusable and nobody seems
to use it actually.
I'm confident this helps,
Andreas
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@documentation.openoffice.org
For additional commands, e-mail: dev-h...@documentation.openoffice.org
