Contact
12 programs
Explore the site

Apache OpenOffice Calc · Workbook organization · Intermediate

Organize Sheets and Navigate with Navigator in OpenOffice Calc

Rename, move and copy Apache OpenOffice Calc sheets, check formula dependencies, and navigate to a named range.

Documentation-qualified exercise; native application verification pending · Editorial standards

Organize a small workbook without confusing sheet order with formula dependencies. You will rename a source sheet, move a summary, copy the source for an experiment, and use Navigator to return to an explicitly named target.

Build two linked sheets

Start a new spreadsheet. Rename its first sheet Inputs by right-clicking its tab and choosing Rename Sheet. Use Insert → Sheet to add one named Summary after Inputs. If the blank workbook already contains other sheets, leave them untouched; the operations below concern only these named sheets.

Enter this complete Inputs fixture:

Cell Content
A1 Item
B1 Units
A2 Desk kits
B2 7
A3 Wall kits
B3 5
A4 Total
B4 =SUM(B2:B3)

On Summary enter A1 Live total and B1 =Inputs.B4. Inputs.B4 and Summary.B1 must both show 12. In Inputs select B2:B4 and choose Insert → Names → Define. Name the range InputTotals, assign it explicitly to $Inputs.$B$2:$B$4, and click Add, then OK. This range is a navigation target, not a range to sum: it contains detail plus a total.

Use File → Save As in ODF spreadsheet format to save organized-sheets-baseline.ods before renaming, moving or copying any sheets. Preserve that file unchanged. Immediately use File → Save As again to create organized-sheets.ods, and perform every remaining step in this working copy. Use short unique sheet names without punctuation here so that reference quoting does not distract from the dependency checks.

Rename a source and inspect the references

  1. Right-click the Inputs tab, choose Rename Sheet, replace the name with Stock, and confirm.
  2. Click Summary.B1 and inspect the formula in the input line. Its source should now refer to Stock.B4 and its result remain 12. Calc may display additional absolute markers; the important check is the correct sheet and cell.
  3. Reopen Insert → Names → Define and select InputTotals. Inspect its assigned reference: it should now point to Stock.B2:B4, not an absent Inputs sheet. Close without changing it if correct.
  4. On Stock change B2 temporarily from 7 to 9. Stock.B4 and Summary.B1 should become 14. Restore B2 to 7 and confirm both return to 12.

If a formula shows a broken reference, stop before doing more renames or moves. Check the actual formula and named definition. For this disposable example you can restore Summary.B1 to =Stock.B4 and the name to $Stock.$B$2:$B$4, then rerun the change test. A visible sheet name alone does not prove every reference is correct.

Move a sheet deliberately

Right-click Summary and choose Move/Copy Sheet. Keep To document set to the current workbook. Clear Copy, select Stock in Insert before, and confirm. Summary should now appear before Stock. Verify Summary.B1 still returns 12 and still refers to Stock.B4. Moving a tab changes its position, not the business meaning of the referenced source.

Use the dialog rather than guessing a drag modifier. The documentation distinguishes Ctrl on common desktop platforms from Option on macOS for a drag-copy operation; choosing Copy in the dialog makes the intention visible.

Copy a source for an isolated experiment

  1. Right-click Stock and choose Move/Copy Sheet again.
  2. Keep the current document selected, enable Copy, and choose the end position in Insert before. Confirm.
  3. Rename the new copy Trial. Check that Stock still exists and that Trial contains the same initial cells and the local =SUM(B2:B3) total formula.
  4. Set Trial.B2 to 20. Trial.B4 should become 25. Stock.B4 and Summary.B1 must remain 12 because the summary still points to Stock.
  5. Inspect Summary.B1 again. If your intended scenario is to summarize Trial instead, explicitly change that formula to =Trial.B4; it should become 25. Restore =Stock.B4 and verify 12 before continuing.

This is a local-formula copy test, not a promise that every reference in a complicated sheet is isolated. A copied sheet can retain references to other sheets. Do not move it into another workbook in this exercise: the AOO guide warns that cross-document moves can create formula-link conflicts.

Find the source with Navigator

Choose View → Navigator (F5 is an alternative on keyboards where it reaches the application). Expand the range category and locate InputTotals, then double-click it to jump to its cells. Verify that the active sheet is Stock and the destination covers B2:B4, not Trial. If the object lists are hidden, use the Navigator’s Contents control to show them.

Expand the sheets list and double-click Summary to return to it. Navigator also has Column and Row boxes for jumping to a cell on the current sheet; these coordinate fields do not by themselves change the source of a formula. Informative names are especially helpful when there are more sheets than visible tabs.

For keeping headings visible while scrolling within one sheet, use the separate freeze panes and navigation guide. Freezing does not reorder, rename or copy a sheet.

Save, reopen and verify the final state

Save and close the ODS, then reopen it. Confirm Summary precedes Stock, Trial still exists, Summary.B1 is 12, Stock.B4 is 12 and Trial.B4 is 25. Recheck the named range through Navigator and inspect the summary formula. Change Stock.B3 to 6: the live total should become 13 while Trial remains 25. Restore Stock.B3 to 5 and save.

If you moved Summary to the wrong position, use Move/Copy Sheet again with Copy cleared. If you accidentally moved Stock rather than copying it, inspect all references before attempting recovery; open the preserved organized-sheets-baseline.ods rather than deleting an uncertain sheet. Verify it still has Inputs and Summary with both totals 12, then use File → Save As to create a fresh working file before repeating the organization steps. After reopening, do not depend on old Undo history. Keep a separate saved copy before experimenting with deletions or cross-workbook moves.

Version limits

The procedure follows English AOO 4.1 desktop menus; native execution and screenshots were not available for this example. Inspect names and formula targets exactly as displayed; do not infer that a renamed tab also renames unrelated text labels. CSV cannot preserve this multi-sheet workbook, its names, or its dependency structure. Keep the ODS master.

Version scope and first-party references

Apache OpenOffice Calc 4.1 desktop, English menu names; documentation checked 2026-10-19. No native build or operating system was tested for this exercise. The expected numbers were calculated independently; appearance, UI interactions and persistence remain reader checks. No screenshot is presented as evidence of running this fixture.

Sources consulted on 2026-10-19: