Collabora Office Calc and Collabora Online Calc · Data entry and validation · Intermediate
Create and Maintain a Shared Drop-Down List in Collabora Calc
Use Data Validity with a named source range, test invalid entries, and maintain shared choices without assuming paste is blocked.
Official documentation reviewed; independent synthetic checks; not tested in Collabora applications. · Editorial standards
Use Data → Validity with a Cell range source for a list that several editors need to maintain. Put the choices in a small named range, then point the input cells at that name. A shared list is workbook data, not a private preference for each editor.
Scope and prerequisites
The criteria and validation behavior below follow Collabora Office 26.04 Help. Online 23.05 release notes independently document the Data–Validity dialog and list-related fixes. Collabora Office Classic is the separate traditional desktop product. The Help label 26.04 identifies the documentation version, not a verified Classic runtime. New Collabora Office 26.04 uses an Online-style interface; traditional menu-bar routes below apply only where the installed interface actually exposes them. Online control placement can vary by server build and interface; mobile apps are not covered by these desktop-browser directions. Work in a disposable ODS copy with editing permission before modifying a team template.
Build the list source
Enter these values in H1:H5 on Sheet1:
| Cell | Value |
|---|---|
| H1 | Allowed status |
| H2 | New |
| H3 | In progress |
| H4 | Blocked |
| H5 | Done |
Set A1 to Task and B1 to Status; put Draft, Review and Send in A2:A4. A plain-text setup file repeats the layout without storing any validation rules.
- Select H2:H5, excluding the header. In Online’s documented Data-tab named-range controls, choose Define and create
StatusChoices. Verify the reference is the absolute range$Sheet1.$H$2:$H$5and use document scope if offered. See Collabora’s Online named-range announcement. The traditional menu-bar Help route is Sheet → Named Ranges and Expressions → Define, only where your installation exposes it. - Select B2:B4 and open Data → Validity. On Criteria, set Allow to Cell range and the source to
StatusChoices. Enable Show selection list. Do not choose List and type the name as a literal list item. - Use Input Help to explain the four choices. In Error Alert, enable the invalid-input message and choose Stop. Warning and Information are different policies and may let an editor proceed.
- Confirm, select a different cell so the changed alert policy takes effect, then return to the inputs. Choose New in B2, In progress in B3 and Done in B4. Try typing Waiting in B2; it should be rejected by the configured Stop rule. Cancel that trial.
For a one-off list, Allow → List with New, In progress, Blocked and Done entered as separate list entries is simpler. A cell source is preferable here because the maintainer can see and edit the vocabulary in one place.
Maintain it with other editors
Agree who maintains H2:H5 and the validity definition. If useful, protect the source range while keeping B2:B4 editable; protection is a separate configuration, not something the dropdown creates.
To add Waiting, enter it in H6 and extend the name to $Sheet1.$H$2:$H$6. Merely typing below a fixed range is not proof that the list expanded. Check every destination cell and ask a second editor to check the new option in the same saved file. Save and reopen before adopting the template. Do not rename an existing choice casually: old entries may need an explicit cleanup rather than changing themselves.
Check pasted and existing data
Collabora’s validity guide warns that copy/paste, drag-and-drop and existing invalid data are not reliably prevented by entry validation. It is not a security boundary. After a bulk import, compare entries against the allowed vocabulary. For example, =COUNTIF(StatusChoices;B2) gives 1 for New and 0 for Waiting before H6 is added; COUNTIF explains the count. Adapt argument separators to your installation.
If the dropdown disappears, check that Show selection list is enabled and the name still resolves. Avoid merged input cells during diagnosis. Test any XLSX export by reopening it: neither a CSV export nor its reimport preserves validity or named-range definitions.
Testing disclosure: Documentation and expected membership checks were reviewed independently. No native Collabora or multi-editor session was run; dropdown behavior, saved-rule persistence and concurrent maintenance must be verified in your deployment.