Assign Name to Cell Range

OpenOffice.org 2.0.x

Document - ID	Specification Owner	Last Change	Status
	Matthias Müller-Prove	8/10/05	final
Conforms to	StarOffice Specification Workflow v0.1, Aug 2003
Applies to	Calc
Task ID(s)	Q-PCD EaseOfUse-MMP-02: i20335 OOo 2.0.2 i53143 (select content of name box on get focus) OOoLater i53146 (Named cell areas)
Category	feature

Abstract

The Name Box – alas the Sheet Area Field – in Calc can be used to define new cell ranges by simply typing a name. The immediate display of a tool tip tells the user that she is about to create a new named cell range.

i-Team Members (The specification owner is part of the i-Team)

	Name	E-mail Address
User Experience	Matthias Müller-Prove /MMP	mprove@sun.com,
Development	Niklas Nebel /NN	Niklas.nebel@sun.com,
Quality Assurance	Peter Junge /PJ	peter.junge@sun.com,
Documentation	Uwe Fischer /UFI	Uwe.Fischer@sun.com
		mail to iTeam

Approved for Implementation

	Approved by	Date
User Experience	Matthias Müller-Prove	06.05.04
Development	Niklas Nebel	06.05.04
Quality Assurance	Peter Junge	06.05.04
Documentation	Uwe Fischer	06.05.04
String Review	Elizabeth Matthis	17.05.04

Document Change History

Rev. Level	Change	Initials	Date
	initial version	MMP	4/21/04
	detailed specification; strings TBD	MMP	5/5/04
	addressed NN-1 to NN-4 approved by iTeam	MMP	5/6/04
1.0	approved by EM, spec is final	MMP	05/17/04
	created task i53143 for focus behaviour of name box (row 70)		

Contents

1	Motivation	4

2	User Scenarios	4

3	Goals	4

4	Requirements and Dependencies	4

4.1	Requirements	4

4.2	Technical Dependencies	4

5	Competitive Analyses	5

5.1	StarOffice 7	5

5.5	MS Excel 2003	7

6	Detailed Specification	8

6.1	Name Box	8

6.2	Creating Named Cell Ranges	8

6.3	G11n	9

6.4	Error Conditions	9

6.5	Migration	9

7	Future Tasks	10

8	Notes	10

8.1	Addressed Issues	10

9	References	11

Glossary

Term	Description
cell area	an arbitrary set of cells
cell range	a rectangular shaped continuous cell area. A single cell can also be a range.
cell reference	a reference to a single cell, e.g. “$A$1”
range reference	a reference to a range depicted be the top left and bottom right cell reference, e.g. “$A$1:$C$3”
range name	a unique name to depict a range reference
area reference	a reference to a cell area. This is a semicolon-separated list of cell references and range references, e.g. “$A$1:$C$3;$E$5”

Motivation

Users of competitive products expect to define a cell range by simply typing a new name into the sheet area field.

User Scenarios

Peter defines named cell ranges in order to improve the legibility of complicated expressions in formulas. By doing so he can simplify e.g. Sum($Sheet1.$F$7:$H$7) to Sum(RevenueInEMEA).

Goals

The creation of a named range should take at most as many steps as in MS Excel.

Requirements and Dependencies

Requirements

Product Requirement: Create regions in Calc by entering an unused name into the sheet area field.

Customer Need/Problem: The user enters a name into the sheet area field to define a new region. The user expects this behavior because some other office applications offer this feature.

Technical Dependencies

n/a

Competitive Analyses

StarOffice 7

Sheet Area Field

The reference of the active cell or selected cell range is displayed in the sheet area field. The range name is displayed here if the user selects a named cell range.

On entering a formula, the user can select one of the ten last-used functions from this drop-down list box. One can include one of the functions in the entry field of the Formula bar. The program notices both entries, from the Function AutoPilots and the direct entries. In the latter, only functions will be considered that are not in parenthesis, for instance, functions that are a parameter of other functions.

Cell references can be entered directly in the sheet area field. For individual cells it is the reference (e.g. F1). For a range reference the cell range (e.g. A1:C4) has to be entered.

Illustration 1 Sheet Area Field left in Formular toolbar of StarOffice 7 (current reference is A2:A4)

If the user enters a name in order to create a named range, and hits <RETURN> StarOffice 7 demands to enter a proper cell reference into the sheet area field.

Illustration 2 Notification dialog to enter a valid reference

Ther German name if the Sheet Area Field is “Tabellenbereich”.

Insert Menu

The Insert menu offers the following set of commands:

Insert>Names>Define...

Opens the Define Names dialog to assigns a named reference for the currently selected range. The reference can also be a area reference; but this has to be entered manually.

Illustration 3 Define Names dialog in StarOffice 7

Insert>Names>Insert..

Inserts a defined named cell range at the current cursor position.

Insert>Names>Create...

Allows to automatically name multiple cell ranges.

Illustration 4 Create Names in StarOffice 7

Insert>Names>Labels...

Illustration 5 Define Label Range dialog in StarOffice 7

Navigator

The Navigator has a section Range names that lists all named cell ranges.

MS Excel 2003

Name Box

The Name Box of MS Excel can be used to define named cell areas. Therefore the user has to activate the text field of the control – the entire content gets selected and the edit mode is active. By typing a new name and leaving the control with <RETURN> the name is assigned to the currently selected cell area. Without the confirmation no new cell area is defined. Such a focus lost can happen if the user opens a menu or decides to select something else.

Illustration 6 Name Box (currently displaying the label “A2”) in MS Excel 2003

The German name of the Name Box is “Namenfeld”.

Detailed Specification

Name Box

The Sheet Area Field control of Calc's Formula Bar (Illustration 1) is renamed to “Name Box”.

According to the current value of the Name Box a tool tip in superscript position is displayed to inform the user about the current command. This is the same behavior as for the formula field.

Illustration 7 Help Text for Name Box

String list

Item	English	German	Comments
label	Name Box	Namenfeld	
tool tip label	Select Cell	Zelle auswählen	
tool tip label	Select Range	Bereich auswählen	
tool tip label	Select Database Range	Datenbankbereich auswählen	
tool tip label	Go To Row	Gehe zu Zeile	
tool tip label	Go To Sheet	Gehe zu Tabelle	Liz: Maybe we'll change it to “Tabellenblatt”in the future, because usability studies have shown that it would be less confusing. But we won't make this term change for OO.o 2.0 because 150 UI strings would have to be changed.
tool tip label	Define Name for Range	Bereich benennen	

“Select Range” is used for range references, as well as for named cell ranges.

Creating Named Cell Ranges

The sheet area field of OOo2.0 can be used to define named cell ranges. Therefore

The user selects a cell area.

The user clicks into the text field of the sheet area field. This activates the text control and the entire content is selected. (OOo 2.0.2: i53143)

The user enters a name.

The user hits <RETURN> or <ENTER> key.

Now the following algorithm is followed to decide if the entered string matches any know object or if it should be interpreted as a new name:

IF the string IS properly formed

THEN

IF the string is any known label

THEN

change selection depicted by label;

ELSE

name= string;

IF the name IS in use for the current document
THEN

select cell range associated with this name;
ELSE

IF selection is properly formed range

THEN

assign name to current selection;

ELSE

show stop alert text 1;

ELSE

show notification alert text 2;

String list

Item	English	German	Comments
alert text 1	The selection needs to be rectangular in order to name it.	Der ausgewählte Bereich muss rechteckig sein, um ihn benennen zu können.	
alert text 2	You must enter a valid reference or type a valid name for the selected range.	Sie müssen entweder einen gültigen Zellbezug oder einen gültigen Namen für den ausgewählten Bereich angeben.	this replaces the sting of illustration 2

Tab Order

n/a

Key Board Shortcuts

<RETURN> and <ENTER> are used to confirm an entry to the Name Box

G11n

n/a

Error Conditions

2 alert boxes are defined at 6.2 Creating Named Cell Ranges.

Migration

n/a

Future Tasks

i53146

For OOo2.o this feature is limited to cell ranges. It is desired to extend this feature to cell areas.

Notes

Addressed Issues

NN-1. Was ist aus der Idee mit der Tip-Hilfe geworden (damit man erkennt, dass man jetzt einen neuen Namen anlegt, wo man sich doch eigentlich nur vertippt hat und etwas anspringen wollte)?

UFI: meinst du den Help Agent nach Ende der Eingabe? Der nervt die User nur - bitte nicht auslösen. Ein versehentlich angelegter benannter Bereich ist ja kein Schaden, vor dem man warnen müsste.

PJ: ... oder vielleicht sollte man den Dialog für Insert->Names->Define... hochreißen?!
Vorteile:
1. Die Sache ist dann klar
2. die weiteren Optionen des Dialoges können genutzt werden

Das vertippen sollte doch eigentlich jeder merken, da das Autocomplete den Dienst verweigert.

MMP: superscript tool tips support the use of the Name Box control.

NN-2. Ich finde nicht, dass das ganze bei TAB aus dem "sheet area"-Feld aufgerufen werden sollte. Das ist eine Funktion, die schon gezielt per ENTER ausgelöst werden sollte.

MMP: ok, just RETURN. No TAB.

NN-3. Anspringen kann man bisher folgendes:
- Zell- oder Bereichsreferenzen
- Benannte Bereiche
- Datenbankbereiche
- Zeilennummer (Spalte bleibt unverändert)
- Tabellennamen
Das sollte wohl alles auch weiterhin möglich bleiben, und ein neuer Name nur angelegt werden, wenn es nichts davon ist.

PJ: An dieser Stelle muss ich mich als Fetischist für 'tabellarische Feature-Übersichtichten' outen. Eine Tabelle, die das hier von Niklas vorgestellte alte Verhalten dem erweiterten Funktionsumfang gegenüberstellt halte ich für wünschenswert in der Spec.

MMP: sure, I changed the algorithm. Peter, see my footnote for labels.

NN-4. Terminologie: Ein "cell range" ist sonst immer und überall ein rechteckiger Bereich, alles andere sind dann mehrere Ranges, oder eine "multiple selection", wenn es die Selektion ist.

UFI: Den Unterschied von Range und Area sollten wir schon durchgehend beachten. Leider ist die Hilfe da immer noch nicht ganz auf Stand: In der alten deutschen Hilfe waren es alles "Bereiche", und es hat uns viel Mühe gekostet, das in der englischen Hilfe auf "Range" und "Area" aufzuteilen. Wie ich es verstehe, ist ein Range ein einziger rechteckiger Bereich von zusammenhängenden Zellen. Eine Area kann jede Art Auswahl von Zellen sein.
Insofern ist "Sheet area" als Name des Controls links in der Formula Bar auch schon merkwürdig. Wäre nicht "Cell range" besser?

Bei Print Ranges gab es neulich eine Diskussion, ob es insgesamt nur einen Print Range gibt oder mehrere davon. Der Menübefehl Format-Print Ranges und der Name des Dialogs Edit Print Ranges legt den Plural nahe, aber ich halte das für einen Bug. M.E. ist "der eine Print Range" der Bereich, der gedruckt wird, egal aus wie vielen Sheets und Cell Areas der besteht. Aber man kann mich eines Besseren belehren.

MMP: yes, now the spec uses the described meaning for area and range. The Sheet Area Field will be renamed to “Name Box”

References

<Give references to related documentation, such as engineering designs, technical specs, and other sources for related units. Also include links to documents that other sections in this spec need to refer to, such as user opinions usability test reports.>

A label can be a cell reference, a range reference, a database range, a row number, or a sheet name.

11	Assign Name to Cell Range

Assign Name to Cell Range	11

http://specs.openoffice.org/calc/ease-of-use/CellRange.sxw

Matthias Müller-Prove

2004-04-21T12:05:50