Collabora Office Calc with the documented Consolidate dialog · Cross-sheet consolidation · Intermediate
Combine Department Sheets by Matching Labels in Collabora Office Calc
Consolidate differently ordered department summaries by row and column labels, with an explicit linked-results test.
Official documentation reviewed; independent synthetic checks; not tested in Collabora applications. · Editorial standards
In a traditional menu-bar Collabora Office Calc installation that exposes the documented dialog, use Data → Consolidate, include the labels in each source range, and consolidate by Row labels and Column labels. This matches categories by their text rather than by their positions. It is useful when department sheets list the same items in different orders.
Scope and starting point
These instructions follow Collabora Office 26.04 Consolidating Data help. 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. That help page does not establish a Consolidate dialog in new Collabora Office, Collabora Online or mobile. Use this procedure only if your actual traditional/Classic installation exposes the documented Data → Consolidate dialog. If it is absent, stop and use a separately verified workflow; switching to an unspecified desktop edition is not a verified fallback.
Use a new ODS workbook with sheets named Design, Support and Summary. Enter these two source blocks in A1:B4 on their respective sheets; the practice text file repeats the layout.
| Design: Item | Amount |
|---|---|
| Paper | 120 |
| Travel | 200 |
| Software | 80 |
| Support: Item | Amount |
|---|---|
| Software | 50 |
| Paper | 30 |
| Travel | 70 |
Expected combined categories are Paper 150, Travel 270 and Software 130, totaling 550. Do not compare by output row position; compare by label.
Consolidate into a separate destination
- Activate the empty Summary sheet and open Data → Consolidate.
- Set Function to Sum. Select
$Design.$A$1:$B$4as a source data area and click Add. Add$Support.$A$1:$B$4as a second area. Ensure both entries appear in Consolidation areas. - Set Copy results to to
$Summary.$A$1. Leave ample space below and to the right. Never use a destination overlapping the inputs or an existing report you need to keep. - Expand More. Under Consolidate by, enable Row labels and Column labels. Both source ranges include the item labels in column A and the Amount header in row 1.
- For the first test, leave Link to source data off. Confirm and compare the result categories with 150, 270 and 130. Use SUM on the three summary amounts to check 550.
Why the labels matter: position-only addition would pair Design’s Paper 120 with Support’s Software 50, giving a misleading 170 in the first position. Label matching instead pairs Paper 120 with Paper 30.
Test a linked version separately
Create another blank output sheet called LinkedSummary. Repeat the consolidation with the same inputs, a destination there, and Link to source data enabled. Collabora documents linked results as formulas with supporting source-reference rows grouped and hidden beneath the summary.
Change Design’s Paper amount from 120 to 125. The linked Paper result should become 155 and its combined total 555. The earlier unlinked result is a snapshot; rerun that consolidation if it needs updating. Restore the input after the test. Save, reopen and repeat a source-value change before relying on the linked report.
Do not type over the generated formulas or assume that adding a new source row extends the configured areas. For a structural change, inspect both source ranges and rerun or rebuild as appropriate, then check totals again.
Diagnose mismatches
The label text must match. Software licences will create a different category from Software; do not silently combine them unless they mean the same thing. Check extra spaces, inconsistent headings and numeric values stored as text. When the result seems doubled, inspect the area list for the same source added twice.
This workflow combines existing summaries. It does not deduplicate transaction records, reconcile currency units, or make an audit trail. Confirm that both departments’ amounts use the same period and units before adding them.
Testing disclosure: Source ranges, matching-label expectations and arithmetic were checked independently against the official Consolidating Data help. This procedure has not been executed in Collabora Office; linked formulas, grouping and persistence require native verification.