Issue 67499 - HEADER: Defining Named-Ranges in Calc-Sheets
Summary: HEADER: Defining Named-Ranges in Calc-Sheets
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.0.3
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2006-07-18 13:06 UTC by discoleo
Modified: 2013-08-07 15:12 UTC (History)
1 user (show)

See Also:
Issue Type: FEATURE
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description discoleo 2006-07-18 13:06:37 UTC
Header for Calc-Worksheets

I often have to define many names/ranges in my Calc worksheets. However, having
many names easily creates confusion. One way around this is presented in issue, which describes the use
of namespaces. A complementary method implies the use of some special headers
for every worksheet containing in a structured format all the definitions. These
headers would be similar to the C++ header files, containing important
definitions and other advanced features. Such a header section allows writing
more structured worksheets and permits retaining the overview over complex
sheets. (As variables and constants are defined here, this could be viewed more
like true C.)

Another disadvantage of the current implementation is the fact that named ranges
DO NOT change automatically when a row/column is deleted/inserted. When this
happens, it is very difficult to seek every name/range for the presence of such
a range that needs to be corrected. This generates very hard to trace errors.
The header would provide such a place, where all names and ranges defined for
one sheet can be easily visualized, allowing greater flexibility in the
management of names and ranges.

Because it is easier to understand this principle when seeing an example, I will
write such a putative header:


// I will also use features described in
// issue 66886 (about Namespaces)
// and also a new feature: function reference()
// (as of this writing not yet posted)

// A very useful feature would be to implement comments
// we could use "C++"-style comments
# or even awk/gawk-style comments
# this way we can have a more structured file

// use namespace all
// the previous command is only for users who do NOT want
//   to learn the power of namespaces
// of course, ANYONE ELSE would make full use of namespaces

// use freely all the names defined in the sheet 'one_sheet'
use namespace 'one_sheet'

// we want to use only some of the names defined in another sheet:
typedef 'another_sheet'::'some_name' 'LocalAlias'
// alternative definition:
// 'LocalAlias' = 'another_sheet'::'some_name';


// I need sometimes named ranges and specific constants
// The header will allow us to visualize all the definitions
//   and to add useful comments, e.g.
// intMyConst specifies the number of ...something
// (const should permit more effective processing)
const intMyConst = 22;
const strMyConst = 'my string';

// some variable and the number of data rows
// see later for a short description of reference()
//   IF a row is inserted or deleted,
//   intTotal is NO longer == 900!!!
intCount = COUNTA(A1:A900);
intTotal  = ROWS(reference(A1:A900, row_delete || row_insert));

// we can also assign names to cell-ranges
// but we will again add some comments,
// specifying what those ranges really represent:

// SOFA score in the first 24 h
SOFAi = $B$1:$B$900;

// maximum SOFA score using the maximum value for any of its subcomponents
SOFAmax = $C$1:$C$900;

// this are the various components of the Charlson Comorbidity Score
CharlsonT = reference(U1:AM900, row_delete || row_insert);
// { I will describe the function reference() as a separate Feature Request;
//   basically it is intended to offer greater control over how the range changes,
//   when a formula using this range is moved/copied to a different cell
//  OR
//   a row/column within this range is deleted/inserted.
// }
// row_delete || row_insert => adapt the range if a row is deleted/inserted, but
//   keep the range unchanged if a formula containing this named range
//   is copied or moved to another cell!
// the implementation of reference() will also depend on this header
//   and additionally on hardlinks (issue 66817)

Comment 1 discoleo 2006-08-06 22:09:21 UTC
The header is meant as a more advanced MANAGEMENT SYSTEM for names/labels (named

The limitation of the classic management system are (aka Names-List):
 - we have a bunch of names from ALL sheets
 - we do NOT have any comment specifying what exactly those names are
 - we do NOT have an OVERVIEW on ALL NAMES AND RANGES; we see only the range for
the currently selected 'Name'

A header would correct all these issues, and it could allow us to extend the
definitions, e.g.:
 - why have a cell having a specific value (e.g. in cell A10: '3'), which is
used then in many formulas as A10, and not define in the header const
'MySpecialNumber' = '3', so it is easily visible and accesible and we would
comment what it really means;
 - use formulas inside the definitions, and so on

Another limitation occurs when a row/column within the range is
deleted/inserted; the names do NOT automatically adapt and hard-to-track errors
occur. For a description of this, see issue .
Comment 2 frank 2006-08-31 12:59:20 UTC
I have no clue how this should work, but let's requirements decide.
Comment 3 discoleo 2006-09-28 01:10:22 UTC

As my previous explanation was not as easy to understand, I'll give another try.

The Problem

I usually work with huge worksheets and have to define many names (i.e. I give
names to many and very various ranges). On a recent worksheet I had some ~70-80
names (there would have been much more, if I wouldn't get disoriented by all
those names).

Having so many names creates some problems:
 1. name conflicts: many names are similar and I easily misidentify them and use
the wrong one
 2. if I delete a row or a column within the named range, the name DOES NOT
automatically adapt to this situation (it cannot adapt, see also issue )

 1a. to have a better understanding for what one specific name stands, there
should be a field to enter a more extensive description (aka a comment)
 1b. restrict names to one sheet, see issue on Namespaces,
 2. the names are usually managed by calling the menu 'Insert -> Names ->
Define', and the "Define Names" window posps up.
  - here you see a list with all the names sorted in ascending order
   -- HOWEVER: you see only the range for the selected name, i.e. I do NOT have
an overview on all Names with their corresponding range visible
   -- e.g., when I select a name ('Charlson05', in the Assigend to I will se
   -- to find however the range of interest, I have therefore to browse through
every name and look for this field
   -- therefore, it is easy to overlook one erroneus range

My solution to this second point would be:
 - to have on the "Define Names" window a button "Advanced"
 - when clicking the "Advanced" button, a simple writer-type document should
open (or script/text- like window, really something very primitive), where one
can write simple PLAIN text (aka C++/C/awk/-style code)
 - the written text would be actually comprised of the definitions for the
Names, e.g.
   Charlson05 = $'2005'.$BO$2:$BO$422
 - because this is plain text, we can easily add comments:
   '# Charlson05 is the Chralson comorbidity score for the 2005 patients'
 - we could use C/C++ style comments, or awk/gawk (as I used previosly)
 - this file looks very similar to the C++ header files, therefore I called this
a "Header", but it may be misleading, because it is not a header in its own way


 - we will have a comprehsive list containing ALL the names AND their
corresponding ranges visible SIMULTANEOSLY (i.e. I do not have first to select
the name to view the range pointed by the name)
   -- I can therefore more easily spot any range that must be corrected
 - we will have extended descriptions for the various names
 - we can group the names the way we wish to do it (and the way it would make
sense): i.e. we won't have necessarily the names in alphabetical order, but
might choose that a different grouping, e.g. all the 19 named subcategories of
the Charlson score together with the Charlson score;
 - there are numerous other advantages, including a more structured document/
spreadsheet (as we can make important definitions here, and do NOT have them
dispersed somewhere throughout the spreadsheet)

See also my pseudo-example of such a "Header" at the top of this page (first post).
Comment 4 discoleo 2008-05-23 22:54:41 UTC
The ODF-solution for issue 66886 as proposed by David Wheeler touches also this
issue, see:

This feature request is aimed to extend the original proposal:
 - the sheet-headers would contain the definitions for the
   locally scoped named-expressions / named-ranges
 - but will extend the concept to allow declaration of locally scoped
   constants / variables
   -- constants / variables are not yet available in spreadsheets

This feature basically will separate the data-layer from the various variables
declared within a spreadsheet. Why mix i.) the data with ii.) the formulas and
with iii.) declarations of other variables.

The i.) and ii.) points are covered in a distinct issue (Multidimensional
Spreadsheets), while this issue covers point (iii).

This will lead to a more structured spreadsheet and reduce the rate of errors.
Also, users should be able to add useful comments to these named ranges and
declared variables (e.g. to specify what the named range / variable actually

e.g. soemthing like:
[sorry IF I srewed the XML up, I don't understand much of it]

<define name="table-table">
 <element name="table:table">
    <ref name="table-named-expressions"/>

      <table:named-range table:name=""
                <comment>Range containing first 100 dates<comment/>
      <table:named-range />
                <comment>Computes locally the time in years<comment/>
      <table:named-expression />
                <comment>Stores locally the time in years<comment/>
      <table:named-constant />