Manage Named and Database Ranges in OpenOffice Calc

Use one name for a calculation input and a separate database range for a record table. Maintaining both boundaries deliberately makes an added record visible to formulas and repeatable data operations.

Build the complete workbook

On Sheet1 enter the following table in A1:C5. Do not include a totals row inside the record block.

Row	A: Ticket	B: Team	C: Hours
1	Ticket	Team	Hours
2	R41	East	6
3	R42	West	9
4	R43	East	4
5	R44	West	7

In E1 type Check and in E2 enter =SUM(C2:C5); the result must be 26. Keep these controls outside the database block. Save as range-boundaries.ods using File → Save As and the ODF spreadsheet format.

Define and use a formula name

1. Select C2:C5, excluding the heading.
2. Choose Insert → Names → Define. Enter WorkHours as the name and inspect Assigned to. Use the explicit absolute reference $Sheet1.$C$2:$C$5; this identifies both the sheet and the cells.
3. Click Add, then OK. Replace E2’s formula with =SUM(WorkHours). It must still return 26.
4. Choose Insert → Sheet and create a sheet named Checks. Enter =SUM(WorkHours) in Checks.A1. The same name should resolve to the original Sheet1 range and also return 26. Put 999 in Checks.C2; the total must remain 26 because the name points explicitly to Sheet1, not the same coordinates on whichever sheet is active.

This is a workbook name with a sheet-qualified target. The guide’s Insert → Names → Define workflow is not a LibreOffice “Manage Names” scope-selector procedure. This tutorial does not promise identically named sheet-local names in AOO. Use distinctive document-wide names and absolute sheet-qualified definitions.

Define the record block separately

1. Return to Sheet1 and select A1:C5.
2. Choose Data → Define Range, name the database range WorkLog, and verify $Sheet1.$A$1:$C$5 as the range. Expand More if needed and enable Contains column labels. Click Add, then OK.
3. Select A1:C5 and choose Data → Sort. Set the first key to Hours, descending. On Options, ensure the range contains column labels. Confirm.
4. Verify the complete rows: R42/West/9, R44/West/7, R41/East/6, R43/East/4. E2 and Checks.A1 should both remain 26. If only the Hours column moved, undo immediately and repeat with all three columns selected.

A formula name identifies values; a database range additionally holds information such as column headings and the most recent sort/filter settings. Neither name turns this into an automatically expanding Excel table. The existing whole-record sorting guide covers sort and filter safety in more detail.

Extend both boundaries and repeat the operation

Enter R45, East and 8 in A6:C6. Before changing the names, both named totals should still be 26: the explicit definition stops at row 5. The sum of all five source records is 34.

1. Open Insert → Names → Define, select WorkHours, change its assigned range to $Sheet1.$C$2:$C$6, use Modify and confirm. Both totals must become 34.
2. Open Data → Define Range, select WorkLog, change its range to $Sheet1.$A$1:$C$6, click Modify and confirm. Preserve the header setting.
3. Select A1:C6 and reopen Data → Sort. Inspect the retained Hours/descending settings rather than assuming them. Confirm the sort.
4. Expect R42/9, R45/8, R44/7, R41/6 and R43/4, with each Team value attached to its Ticket. Both named totals must remain 34.

If your build does not retain the settings after changing the database boundary, reselect Hours and descending in the Sort dialog. The result and full record selection matter more than saving a click. Do not include E1:E2 in the sorted range.

Advanced maintenance and recovery

Change the hours for R45 from 8 to 10 by finding the ticket after sorting, rather than assuming it is still in row 6. Both named sums should become 36. Sort the full record block again and R45 should move to the top. Restore that record’s hours to 8 and sort again; the total returns to 34.

Save, close and reopen the ODS file. Inspect both definitions, both totals, and the row order. Then change one source hour temporarily and confirm both sheets recalculate. Restore the original value and save. These are reproducible checks, not claims of a native test here.

To recover a misdefined name, correct Assigned to in the same dialog; do not delete names that existing formulas use. To roll back the added record, use this complete recovery sequence:

1. Use File → Save As to create range-boundaries-rollback.ods, leaving the five-record working file intact.
2. On Sheet1 find ticket R45 in column A and verify its whole record is R45 / East / 8. After the restored descending sort it should be in row 3, not row 6. Select that record’s row heading and choose Edit → Delete Cells to delete the whole selected sheet row. Do not delete only its Hours cell. The check cells E1:E2 are above this expected row and remain intact.
3. Open Insert → Names → Define, select WorkHours, explicitly restore Assigned to to $Sheet1.$C$2:$C$5, click Modify, then OK.
4. Open Data → Define Range, select WorkLog, explicitly restore its range to $Sheet1.$A$1:$C$5, retain Contains column labels, click Modify, then OK.
5. Select A1:C5 and sort Hours descending with the header option enabled. Verify all four complete records: R42 / West / 9, R44 / West / 7, R41 / East / 6, R43 / East / 4. Both Sheet1.E2 and Checks.A1 must return 26. Confirm that R45 is absent and no original ticket is missing.

If R45 is not in the expected row, locate it by its ticket and inspect the surrounding cells before deleting any whole sheet row. If removing that row would also remove your controls, rebuild the original four-record fixture in a separate copy instead, restore both definitions as above, and verify the same 26 totals. Rebuilding from the fixture is safer than guessing after an incorrect partial-column sort. For a source name used in dropdowns, see validity lists from another sheet.

Version and data limits

These menus are the AOO 4.1 desktop menus. Sheet names with spaces require quoting in references; this example avoids that complication. A name typo can produce an unknown-name error. Numeric text may be ignored by SUM, so compare the individual hours as well as the aggregate. ODS is the master format; do not assume a CSV or another application’s workbook preserves range definitions.

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:

• Apache OpenOffice Named Range .
• Apache OpenOffice Database Range .
• Apache OpenOffice AOO41CG.pdf .