Jean Hollis Weber wrote:
Unfortunately, I am so ignorant of spreadsheet concepts, I am having
difficulty understanding either the Help or Andreas' explanation.
Here is the exact situation that brought up my question:
In Chapter 9 of the Calc Guide (Data Analysis) is a section discussing
Data > Consolidate. This section includes this sentence about the
Consolidate dialog:
"The Source data range list contains any existing named ranges (created
using Data > Define Range) ..."
And in another sentence, "If you are continually working with the same
range, then you probably want to use Data > Define Range to give it a
name."
The entire F1-help on Calc is a litter box of misconceptions and the
whole bouquet of features below the Data-menu contains some of the worst
design flaws in the whole office suite.
This part of Calc is the most important one in a world where 90% of
users desparately try to use Calc as a database rather than calculator.
A "database range" (described by Andreas as "one particular rectangle of
cells by means of an absolute reference $SheetName.$A$1:$X$99") seems to
fit in the context of the chapter, because the Consolidate dialog shows
ranges defined in that format.
Slightly OT excursion:
A "database-like list" which makes a useful database range and a proper
database table as well. All list related functions and features assume
this structure where each row describes exactly one item(measurement,
payment, whatever):
Date Name Value
13/01 Carl 12
14/01 Carl 11
13/01 Mary 10
Exactly the same information in a non-list, a cross table where each
value is found by row label and column label:
Carl Mary
13/01 12 10
14/01 11
This type of table is difficult to evaluate, particularly when cluttered
across sheets.
But one reviewer said about both these sentences, "Surely this should be
"created using Insert > Names > Define..." instead of "created using
Data > Define Range".
So which is correct in this situation? Thank you.
IMHO, everything in the Data-menu _should_ refer to database ranges.
This overall concept with clearly separated database-like lists and
special properties has been implemented incompletely.
- Data>Define|Select|Refresh refer to the db-ranges directly.
- Sort|Filter|Subtotals store all their settings in a database range and
Data>Refresh refers to all these settings. Macro developers may have an
clearer concept of this, but it affects the avarage user as well.
- The consolidation dialog shows both types of named references in a
list box, the db-ranges and absolutely named ranges as well. But you are
free to use any range address SheetX.A1:X99 freely. It is always taken
as absolute reference $SheetX.$A$1:$X$99 pointing to a database-like list.
Macro developers may wonder why the target range of a consolidation is
not a database range with all the consolidation options saved as for the
filter/sort/subtotal descriptors since the consolidation tries to
aggregate cluttered lists into one list.
In fact you can define arbitrary consolidation ranges (assumed lists)
such as $SheetX.$A$1:$X$99 and the resulting range is always a list, but
unfortunately completely unrelated to the db-ranges feature.
- Same question with data pilots. The source of a pilot is always a
database-like list, but the pilot feature is not at all related to
db-ranges.
- Data>Validity and Data>Multiple ranges have absolutely nothing to do
with lists and deserve to be moved to menu Tools or something.
- Outlining and grouping is applies to db-lists in most cases, but not
necessarily. For instance, grouping is an important option in pilots
(cross-tables derived from lists).
- Linked or copied Base tables have no menu item. They create database
ranges on the fly ("Import1", "Import2", "Import3",...). Three of the
four extra options for a database range affect import ranges only.
Meanwhile I could write dozends of pages on Calc's data menu and all the
well known issues related to it and why Calc is still a horrible list
keeper.
IMHO the whole concept, treating lists as special ranges, is the right
way to go while staying in touch with the Excel universe.
Seemingly the spreadsheet developers have forgotten why it has been
introduced in the first place (was it SO 5.x ?).
Contrary to list ranges which are somewhat "borrowed from the database
world", named references are a typical spreadsheet feature.
They are no more and no less than aliases for _arbitrary formula
expressions_ independent from any table structure. Such an alias can
point to an absolute range, multiple ranges, relative ranges, constant
values (numbers and text) and calculated values and ranges as well.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@documentation.openoffice.org
For additional commands, e-mail: dev-h...@documentation.openoffice.org
