Create an Advanced Filter from a Criteria Range in OpenOffice Calc

An Advanced Filter stores its rules in worksheet cells. Criteria on one row are combined with AND; alternative criteria rows are combined with OR. This makes a two-branch extraction reproducible, provided the headers and range boundaries are exact.

Scope and separate work areas

Apache OpenOffice Calc 4.1 desktop, English Windows/Linux menus; documentation checked against the official 4.1 Calc Guide and versioned 4.1.6 Help on 19 October 2026. No native application run or save/reopen test was performed for this exercise.

Keep raw data on one sheet and criteria on another, so an in-place filter cannot hide the controls you need to edit. The exercise copies matches into a third sheet instead of editing filtered source rows.

Create or rename three sheets Orders, Criteria and Matches. In Orders, enter this complete table at A1:E9:

ID	Region	Product	Amount	Status
201	East	Cable	120	Open
202	West	Case	85	Closed
203	East	Stand	160	Open
204	North	Cable	95	Open
205	West	Hub	140	Closed
206	East	Clip	60	Closed
207	East	Case	150	Open
208	North	Hub	140	Closed

Amounts are numeric integers. The eight IDs are unique and their amounts total 950. Leave Matches completely empty and save advanced-before.ods.

On Criteria, put Region in A1, Amount in B1 and Status in C1. The headings must match Orders exactly, including spelling and spaces. Enter East, >100 and Open in A2:C2 respectively. Enter >100 as literal criterion text, without an equals-sign formula. Leave row 3 empty for now.

First extraction: all conditions on one row

1. Select Orders.A1:E9, including its header.
2. Choose Data → Filter → Advanced Filter.
3. Set Read filter criteria from to $Criteria.$A$1:$C$2. You can use the range selector to pick those cells rather than typing the reference.
4. Expand More. Enable Range contains column labels and Copy results to.
5. Set the destination to $Matches.$A$1. Leave Regular expression, No duplication and Keep filter criteria off for this exercise. Leave case sensitivity off; no case-only distinction is used.
6. Confirm. Go to Matches and inspect the complete output.

Expected matches, in original source order, are:

ID	Region	Product	Amount	Status
201	East	Cable	120	Open
203	East	Stand	160	Open
207	East	Case	150	Open

Including headings, the output occupies A1:E4. Sum D2:D4 and expect 430. ID206 is East but fails both Amount and Status; ID204 is Open but fails Region and Amount. These nonmatches demonstrate why the three same-row conditions mean AND.

The official Advanced Filters guide explains the row logic. The versioned filter-options Help supplies Copy results to, column labels, duplicate suppression and the database-range prerequisite for retained criteria. We leave that retained-range mechanism off and rerun explicitly.

Add an OR branch

On Criteria, enter West in A3, >100 in B3 and Closed in C3. Do not put West into another column on row 2; it is an alternative branch, not another condition that a single Region cell must satisfy.

Before rerunning, clear the old output contents from Matches.A1:E9. This area was reserved for results; do not place audit controls inside it. Select Orders.A1:E9 and reopen Advanced Filter. Change the criteria range to $Criteria.$A$1:$C$3, keep the same copy destination and other options, then run again.

Expected IDs are 201, 203, 205, 207, with amounts 120, 160, 140, 150, total 570. ID202 is West/Closed but its 85 fails the second branch’s >100 test. ID208 is Closed with 140 but its North region fails both branches. The result should occupy Matches.A1:E5.

This is (East AND Amount>100 AND Open) OR (West AND Amount>100 AND Closed). The second row does not inherit conditions from the first. If you leave a criterion blank, you have deliberately omitted that restriction from that branch.

Tighten a condition and prevent stale matches

Change Criteria.B2 to >150. Keep the West branch unchanged. Clear the whole previous output area Matches.A1:E9, reselect Orders.A1:E9, verify criteria A1:C3 and rerun.

Now only 203 and 205 match, amounts 160 and 140, total 300. ID207’s amount is exactly 150, so it fails >150. This equality boundary distinguishes > from >=.

The cleared output matters: a smaller new result must not be confused with leftover rows from a larger previous extraction. Check Matches.A4:E9 is empty after the two-record result, with the header and two matches in A1:E3. If you keep a sum outside the output, such as G2=SUM(D2:D9), its expected value is 300 only because the old cells were cleared.

For a further controlled boundary test, change B2 to >=150, clear and rerun. Expect 203, 205, 207, total 450. Keep the criteria and audit notes on separate sheets or outside the reserved output block.

Extend the source deliberately

After the >=150 test, append 209, West, Cable, 110, Closed to Orders.A10:E10. The source total becomes 1060. Clear Matches.A1:E10 and rerun with the expanded source Orders.A1:E10, while keeping criteria A1:C3.

Expected IDs are 203, 205, 207, 209, total 560. The output needs A1:E5 including its heading. If ID209 is absent, first check whether the Advanced Filter source still ends at row 9. Appending a row does not justify assuming every stored range or audit formula grew with it.

Recovery, saved checks and failure cases

Source records should remain unchanged throughout. Inspect all Orders rows and compare individual IDs/fields, not just the total. If an in-place filter was used accidentally, select the source range and choose Data → Filter → Remove Filter to restore visibility. Remove Filter does not delete an extracted copy on Matches. Do not cut, move or bulk-edit a partially hidden source.

Save as advanced-result.ods, close and reopen, then confirm the latest criteria, complete source and copied results. Change one criterion and rerun to prove the selection is reproducible. This is a reader acceptance check; no native persistence result is asserted.

• All rows or unexpected rows appear: inspect blank criterion rows accidentally included in the range and missing conditions in an OR branch.
• Headers are treated as records: enable Range contains column labels and include row 1.
• No rows match: compare heading spelling, numeric Amount types and the selected criteria block. Do not replace source values to force a match.
• Counts shrink unexpectedly: No duplication may be on, or a source/criteria range may be too short.
• Unexpected pattern matches: turn Regular expression off for literal Region/Status tests.
• Source overwritten: Undo immediately or recover advanced-before.ods; destination and source must not overlap.

For ordinary sorting and AutoFilter, use sort whole records safely. Its earlier native screenshots do not establish that this Advanced Filter workflow was run. All totals and expected record selections here are independently computed controls.