Apache OpenOffice (AOO) Bugzilla – Issue 67499
HEADER: Defining Named-Ranges in Calc-Sheets
Last modified: 2013-08-07 15:12:27 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 http://www.openoffice.org/issues/show_bug.cgi?id=66886, 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: // HEADER START // 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 // NAMESPACE EXAMPLES // 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'; // DEFINITIONS/ ASSIGNMENTS // 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) // END HEADER
The header is meant as a more advanced MANAGEMENT SYSTEM for names/labels (named ranges). 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 http://qa.openoffice.org/issues/show_bug.cgi?id=68202 .
I have no clue how this should work, but let's requirements decide.
IMPORTANT EXPLANATION ===================== 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 http://qa.openoffice.org/issues/show_bug.cgi?id=68202 ) SOLUTION ======== 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, http://qa.openoffice.org/issues/show_bug.cgi?id=66886 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 "$'2005'.$BO$2:$BO$422") -- 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 ADVANTAGE ========= - 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).
The ODF-solution for issue 66886 as proposed by David Wheeler touches also this issue, see: http://lists.oasis-open.org/archives/office/200608/msg00001.html 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 represents). 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"> [...] <optional> <ref name="table-named-expressions"/> <table:named-expressions> <table:named-range table:name="" table:cell-range-address="$D$2:$D$101" <comment>Range containing first 100 dates<comment/> <table:named-range /> <table:named-expression table:name="MakeFour" table:expression="2+2"/> <comment>Computes locally the time in years<comment/> <table:named-expression /> <table:named-constant table:name="LocalTimePeriod" table:expression="4"> <comment>Stores locally the time in years<comment/> <table:named-constant /> </table:named-expressions> </optional> </element> </define>