Design/Whiteboard/Calc Range Names

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Summary

This page works out the interaction details for handling the Calc range names. The underlying functionality has already been introduced in LibreOffice 3.4.

This page is currently in work.

Team:

Development: Markus Mohrhard, Kohei Yoshida
Design (UX): Christoph Noack, Astron

(todo: involve Documentation, Localization, QA, Accessibility)

Requirements and Constraints

Scope

The LibreOffice 3.4 new features and fixes page mentions: "Named ranges in sheet-local scope: Previously, Calc only supported named ranges in global scope, that is, all named ranges were made available for the entire document regardless of the current sheet. In 3.4 Calc now supports named ranges that are local to a sheet. The named range dialog (Insert->Names->Define) has been modified to reflect this change. (Kohei Yoshida)"

The old functionality (LibreOffice < 3.4) is described in the LibreOffice Calc Help (Calc/Names).

The scope of this activity is to:

Make the range name dialogs and its interaction more understandable and helpful for the user
Adapt the integration of range names in the Calc Name Box
Adapt the integration of range names in the Navigator
Propose small (!) refinements for related functionality

Discussion

The thread Sheet local range names in Navigator covers most of the discussion on the feature
At the Hackfest2011 an initial UI design has been worked out by Markus and Christoph

Terminology

Range names vs. Names: In Calc both "Range Names" and "Names" are used. To avoid confusion with other ranges Calc offers additionally, we decided to use "Range Names" in general, but we'll shorten that to "Names" in the dialog or once range names have been introduced earlier (e.g. in documentation).
Spreadsheet document vs. Document vs. Workbook : Due to the history of Calc and the "compatibility" with major competitors, all these names are used to refer to the document the user can work on. We decided to go for "Spreadsheet document" or "Document" if space requires us to do so.
Terms Global and Local: The terminology to refer to the scope of the ranges is pretty technical. We try to avoid those terms or will provide them additionally: Global --> Spreadsheet, Local --> $SHEETNAME

Interaction Design

Mockup

DesignTeam Mockup Calc RangeNames.png

General

Keep today's functionality. That includes e.g. that it is possible to also define names for Calc formulas within this dialog (although this is a bit hidden, technically the "range" is a more general "expression")
Since the new scope feature increases the complexity of the today's UI, the functionality will get broken down into Dialog "Define Name" and Dialog "Manage Names"
The UI elements in Dialog "Define Name" and Dialog "Manage Names" are equal (order, naming) to ease understandability for the user
Error handling (especially when defining names) is currently very limited from the user's point-of-view. We'll keep the error dialogs we have today. (Decision Markus/Christoph, Hackfest 2011)
The insert names functionality will be improved in the Dialog "Paste Names"
Common keybindings will be kept
Range options won't be changed

Item: Application Menu

Menu Item Caption	Description	Comments
Define...	Caption unchanged
Manage...	New entry
---	Divider Line
Paste Names...	Replacement for "Insert...".
(remaining items are unchanged)

Notes:

The keybinding is not shown in the mockup, but will be kept.

Item: Name Box

Behavior:

Basic behavior of the name box is kept (for cell non-editing mode)
New: All range names are added from all scopes
New: Composing of the list elements...
- Global names: "%RANGENAME"
- Local names: "%RANGENAME (%SHEETNAME)"
Kept: alphabetical order of the list elements (A->Z order)
New: The last item is "Manage Names..." to provide the user additional hints what this box is about, and to have a shortcut to the corresponding dialog. The behavior is similar the "More..." item in Writer's "Apply Style..." drop-down box (formatting toolbar).
New: If the user clicks into the "Name Box" text edit area, then the whole text gets selected. (Note: If the user exits the text edit area, then any selected text gets unselected.) (missing)
New: If a cell is being edited, then selecting an item will paste the currently selected name at the current cursor position. (edited cell: either in the Input line or directly in the cell).

Things not to get lost:

How many items are shown per default (height of the drop-down list) if many names are defined. -> Eike: 10 items

Implementation status: Partly implemented.

Item: Navigator

Behavior:

Basic behavior of the Navigator is kept
New: Composing of the list elements --> see item Name Box
Additional refinement: Rename "Database Ranges" to "Database ranges" according terminology style guide.

Implementation status: Implemented.

Item: Dialog "Define Name"

Implementation status: Open

Behavior

todo: Add missing details. For the general behavior, please have a look at Dialog "Manage Names".

Button "Add": If all entries are valid, then the changes are applied and the dialog is closed.
Button "Cancel":
- Does not save changes made during this dialog session and closes the dialog.
- If the user does not edit values in any of the Text Fields or the Drop-Down, then key "ESC" is bound to the Cancel functionality.

Error Cases

General:

Input Field "Name":
- If this input field contains an technically invalid name, then both disable the Button "Add" and show the corresponding message symbol / text message in the Info Text area (see table below).
- Note: If possible, the validity of the name should already be check when the user is entering the name.
Input Field "Range" (todo: decide on solution proposal):
- Solution Proposal 1: If this input field contains an invalid expression, then show the (already available) warning dialog after the user activated Button "Add"
- Solution Proposal 2 (recommended): If this input field contains an technically invalid expression, then both disable the Button "Add" and show the corresponding message symbol / text message in the Info Text area (see table below).
If more than one error case (described above) is true and e.g. several messages need to be shown in the Info Text area, then handle the errors according to the order of the input fields.

Error Case	Error Sub-Case	Message Symbol	Message (en-US)	Comment
(none)	(none)	no	Define the name and range or formula expression.	The default message to make the user aware of the flexibility of the range field. Note: Only used for the Dialog "Define Name".
Invalid name in Input Field "Name"	Wrong character used	yes	Invalid name. Only use letters, numbers and underscore.	todo: Confirmation by dev required
Invalid name in Input Field "Name"	Range name already in use (for this scope)	yes	Invalid name. Already in use for the selected scope.	todo: Confirmation by dev required
Invalid name in Input Field "Name"	(todo: good description)	yes	Invalid name. Don't use names like cell addresses.	todo: Confirmation by dev required
Invalid expression in Input Field "Range"	todo	todo	todo	todo

Implementation status: (Implemented)

Item: Dialog "Manage Names"

Dialog:
- New: If the dialog is opened for the first time in this session, then the first item in the Table "Named Ranges" gets selected. (missing)
- New: If the dialog is opened for the first time in this session, then the Group "Range Options" is collapsed. (missing)
- New: If the dialog is opened for the second time (or after), then the last selected item keeps its selection, and is show (if required: scroll to that position). (missing)
- New: If the dialog is opened for the second time (or after), then the Group "Range Options" remembers its last state. (missing)
Table "Named Ranges"
- In general, the currently selected item is always synchronized with the items Text Entry "Name", Text Entry "Range", Drop-Down "Scope" and all items in the Group "Range Options" (missing)
- If more than one item is selected, then: (mostly implemented)
  - ... Text Entry "Name", Text Entry "Range", Drop-Down "Scope" and all items in the Group "Range Options" are inactive (grayed out)
  - ... Text Entry "Name" shows "(multiple)"
  - ... if the Ranges are equal among all selected items, then the Range value is shown in the Text Entry "Range"
  - ... if the Ranges are not equal among all selected items, then the Text Entry "Range" shows "(multiple)"
  - ... if the Scopes are equal among all selected items, then the Scope value is shown in the Drop Down "Scope"
  - ... if the Scopes are not equal among all selected items, then the Text Entry "Scope" shows "(multiple)"
  - ... (for all items in the Group "Range Options") if the option is equal among all selected items, then the option value is shown
  - ... (for all items in the Group "Range Options") if the option is not equal among all selected items, then the the value indifferent is shown
Info Text:
- If one item in the Table "Named Ranges" is selected, then show "Select cells in the document to update the range."
- If multiple items in the Table "Named Ranges" are selected, then show nothing.
- If the Text Entry "Name" is being edited and this field contains an error, then show the error message. (Note: see mockup). (implemented)
- If the Text Entry "Range" is being edited and this field contains an error, then show the error message.
- The Info bar needs to be able to expand from one to more lines (depending on locale) [added by Astron]
- Else: show nothing
Text Entry "Name": todo (e.g. error handling)
Text Entry "Range" todo (e.g. error handling)
Scope: todo
Group "Range Options": todo
Button "Add...": (implemented)
- New: Calls the Dialog "Define Name" which gets opened as a child window. (implemented differently: Define Name replaces Manage Names and opens Manage Names again afterwards to not waste screen space)
- Note: If possible, open the dialog centered to the Dialog "Manage Names". (missing, although since the new dialog now replaces the old one, it might actually be more appropriate to position its upper left corner in the same space were the old dialogues upper left corner was [Astron])
- Note: If the user successfully added a new range name via the Dialog "Define Name", then the currently added named range gets selected in the Table "Named Ranges" (if required: scroll to that position).
Button "Remove":
- New: The currently selected items in the Table "Named Ranges" get deleted.
- Note: For the deletion, no confirmation dialog is shown.
- New: After the deletion, there is a need to select an item in the Table "Named Range". The selection moves to the row position of the (recently deleted) uppermost item. (Example: If the three items in rows 2,6,11 are deleted, then the item in row 2 gets selected after the deletion). (missing)
Button "Select Range": (not implemented: might be unnecessary, as the user already has a visualization; how well the current visualization works for users is hard to say, though [Astron])
- Note: Currently the range is only visible to the user, if the current Calc viewport contains that cell range. The new Button "Select Range" lets the user quickly "jump" to that cell range - to enable a visual check or to provide similar functionality like Item "Name Box" or Item "Navigator".
  - Crazy idea: if such a button were to actually be implemented, it could also zoom in/out to fit tiny/huge ranges [Astron]
- Active, if one item in the Table "Named Ranges" is selected which is a valid cell range (in contrast to a formula expression).
- Inactive (grayed out), if multiple items in the Table "Named Ranges" are selected.
- Selects the Range of the currently selected item and shows it (if required: change the worksheet / scroll the worksheet)
- This is the default button for this dialog:
  - Key "Enter" is bound to the Paste functionality.
  - Double clicking an item in the Table "Named Ranges" executes the Select Range functionality for this item.
Button "OK": (implemented)
- All changes to the named ranges get applied and the dialog is closed.
- Note: If a selection in the document exists, then this selection is kept.
Button "Cancel": (implemented)
- Does not save changes made during this dialog session and closes the dialog.
- If the user does not edit values in any of the Text Fields or the Drop-Down, then key "ESC" is bound to the Cancel functionality.
- Note: If a selection in the document exists, then this selection is kept.

Implementation status: Started

Item: Dialog "Paste Names"

Replaces the today's Dialog "Insert Name"

Behavior:

New: Modal dialog
Dialog:
- New: If the dialog is opened for the first time in this session, then the first item in the Table "Named Ranges" gets selected.
- If the dialog is opened for the second time (or after), then the last selected item keeps its selection, and is show (if required: scroll to that position).
New: Table "Named Ranges"
- (Note: At least one item is always selected in this dialog.)
Button "Paste"
- New: This is the default button for this dialog (key "Enter" is bound to the Paste functionality)
- New: If the user double-clicks on an entry in the Table "Named Ranges", then Insert is executed for the double-clicked item
- Kept: If one item in the Table "Named Ranges" is selected and a cell is being edited, then paste "$RANGENAME" at the current cursor position.
- Kept: If one item in the Table "Named Ranges" is selected and the currently selected cell is not being edited, then replace the cell content with "=$RANGENAME".
- New: If multiple items in the Table "Named Ranges" are selected and a cell is being edited, then paste all selected names (with white space) into the cell (Example: For three range names, the result is "$RANGENAME1 $RANGENAME2 $RANGENAME3)".
- New: If multiple items in the Table "Named Ranges" are selected and the currently selected cell is not being edited, then replace the cell content with "=" and all selected names (with white space). (Example: For three range names, the result is "= $RANGENAME1 $RANGENAME2 $RANGENAME3")
Button "Insert All":
- Kept: Behavior is the same like in the current Dialog "Insert Name" dialog.
Button "Help": Opens the dedicated page in the online help.
Button "Close":
- Closes the dialog. (Note: The editing status of a cell is kept.)
- Key "ESC" is bound to the Close functionality.

Things not to get lost:

(Documentation): Please consider to add a "hint" to the online help that names can also be pasted via the Item "Name Box".

Implementation status: Open

Proposals for Future Improvements)

See the mockup part Dialog "Manage Names" (Future Improvements):

Add filter and search box
Improve the visualization of name / range issues (e.g. if unsupported characters for the name are used) --> non-modal dialogs
Re-think the position in the application menu
Try to merge the similar database ranges with the range names

Realization and Roadmap

Just a rough list at the moment ...

Stabilize this wiki page (Markus, Christoph)
Ask Kohei for his guidance / opinion on this proposal (Markus, Christoph)
Start developing (Markus)
Ping Documentation for terminology check, documentation (Christoph)
Ping Localization for terminology check (e.g. length of the strings) (Christoph)
Ping QA for test cases and "being prepared" (Christoph)

Open Points and Known Issues

Items Dialogs: The functionality of directly adding or modifying the range options may be changed towards a separate popup dialog. This makes the behavior more clear to the user, but requires additional steps.
Item Dialog "Define Name": The button caption "Add" may look weird if only this dialog is used, but is consistent to the dialog for managing names. (Alternatives: OK, Insert, Define).
Item Dialog "Manage Names": Due to potential performance issues, it has been requested by the development that (multiple) name changes will be applied once. -> This requires the use of a modal dialog; better would be a non-modal dialog which also provides Paste Names functionality.
General: The term "range name" or "named range" is a misnomer, because technically it is a named expression. Assumption: LibO/OOo/Excel user know "named ranges", and those user primarily use it to name cell ranges. Using those names for expressions needs much more abstraction capability ... so maybe it would be good to ping the documentation to add a "hint message" in the online help? Furthermore, the corresponding tooltips might refer to both "range/expression".
General: Tooltip definitions are missing on the whitepage.
Item "Dialog "Paste Names": Does it make sense to enable a quick-jump to the Dialog "Manage Names"? User may see a name that needs updating ... But, if we add that button, then we have two dialogs with overlapping functionality :-\
Item "Navigator": The "Edit" functionality for the selected named range is missing ...