Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




OpenOffice Calc 3.x Guide
Previous Page Home Next Page

An advanced filter supports up to eight filter conditions, as opposed to the three supported by the simple filter. The criteria for an advanced filter is stored in a sheet. The first step in creating an advanced filter is entering the filter criteria into the spreadsheet.

  1. Select an empty space in the Calc document. The empty space may reside in any sheet in any location in the Calc document.
  2. Duplicate the column headings from the area to be filtered into the area that will contain the filter criteria.
  3. Enter the filter criteria underneath the column headings (see Table 4). The criterion in each column of a row is connected with AND. The criteria from each row are connected with OR.

Table 4. Example advanced filter criteria

Name Test 1 Test 2 Quiz 1 Quiz 2 Average Grade
="Andy" >80
Image:Tip.png Define named ranges to reference your advanced filter criteria and any destination ranges for filtered data. Each appropriately configured named range is available in drop down list boxes in the Advanced Filter dialog.

After creating one or more sets of filter criteria, apply an advanced filter as follows:

  1. Select the sheet ranges that contain the data to filter.
  2. Use Data > Filter > Advanced Filter to open the Advanced Filter dialog.
  3. Select the range containing the filter criteria and any other relevant options.
  4. Click OK.
Apply an advanced filter using a previously defined named range.

Applying an advanced filter using a macro is simple (see Listing 8). The cell range containing the filter criteria is used to create a filter descriptor, which is then used to filter the range containing the data.

Listing 8. Use an advanced filter.

Sub UseAnAdvancedFilter()
  Dim oSheet     'A sheet from the Calc document.
  Dim oRanges    'The NamedRanges property.
  Dim oCritRange 'Range that contains the filter criteria.
  Dim oDataRange 'Range that contains the data to filter.
  Dim oFiltDesc  'Filter descriptor.
  REM Range that contains the filter criteria
  oSheet = ThisComponent.getSheets().getByIndex(1)
  oCritRange = oSheet.getCellRangeByName("A1:G3")
  REM You can also obtain the range containing the 
  REM filter criteria from a named range.
  REM oRanges = ThisComponent.NamedRanges
  REM oRange = oRanges.getByName("AverageLess80")
  REM oCritRange = oRange.getReferredCells()
  REM The data that I want to filter
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oDataRange = oSheet.getCellRangeByName("A1:G16")
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
End Sub

Change properties on the filter descriptor to change the behavior of the filter (see Table 5).

The filter created in Listing 8 filters the data in place. Modify the OutputPosition property to specify a different output position (see Listing 9). The filter descriptor must be modified before the filter is applied.

Table 5. Advanced filter properties.

Property Comment
ContainsHeader Boolean (true or false) that specifies if the first row (or column) contains headers which should not be filtered.
CopyOutputData Boolean that specifies if the filtered data should be copied to another position in the document.
IsCaseSensitive Boolean that specifies if the case of letters is important when comparing entries.
Orientation Specifies if columns ( or rows ( are filtered.
OutputPosition If if CopyOutputData is True , specifies the position where filtered data are to be copied.
SaveOutputPosition Boolean that specifies if the OutputPosition position is saved for future calls.
SkipDuplicates Boolean that specifies if duplicate entries are left out of the result.
UseRegularExpressions Boolean that specifies if the filter strings are interpreted as regular expressions.

Listing 9. Copy filtered results to a different location.

  REM Copy the output data rather than filter in place.  
  oFiltDesc.CopyOutputData = True
  REM Create a CellAddress and set it for Sheet3,
  REM Column B, Row 4 (remember, start counting with 0)
  Dim x As New
  x.Sheet = 2
  x.Column = 1
  x.Row = 3
  oFiltDesc.OutputPosition = x

(Warning, advanced material.) The OutputPosition property returns a copy of a struct. Because a copy is returned, it is not possible to set the individual values directly. For example, oFiltDesc.OutputPosition.Row = 2 does not work (because you set the Row on the copy to 2, but do not change the original).

OpenOffice Calc 3.x Guide
Previous Page Home Next Page

  Published under the terms of the Creative Commons License Design by Interspire