Calc Guide

DataPilot functions in detail
This part describes the use and options of the DataPilot in detail.
The database (preconditions)
The first thing needed to work with the DataPilot is a list of raw data,
similar to a database table, consisting of rows (data sets) and columns
(data fields). The field names are in the first row above the list.
The data source could be an external file or database. For the simplest
case, where data is contained in a Calc spreadsheet, Calc offers sorting
functions that do not require the DataPilot.
For processing data in lists, the program needs to know where in the
spreadsheet the table is. The table can be anywhere in the sheet, in
any position. A spreadsheet can contains several unrelated tables.
Calc recognizes your lists automatically. It uses the following logic:
Starting from the cell you’ve selected (which must be within your list),
Calc checks the surrounding cells in all 4 directions (left, right, above,
below). The border is recognized if the program discovers an empty
row or column, or if it hits the left or upper border of the spreadsheet.
This means that the described functions can only work correctly if
there are no empty rows or columns in your list. Avoid empty lines (for
example for formatting). You can format your list by using cell formats.
Rule
No empty rows or empty columns are allowed within lists.
If you select more than one single cell before you start sorting, filtering
or calling the DataPilot, then the automatic list recognition is switched
off. Calc assumes that the list matches exactly the cells you have
selected.
Rule
For sorting, filtering, or using the DataPilot, always select only
one cell.
A relatively big source for errors is that you might declare a list by
mistake and you sort your list. If you select multiple cells (for example,
a whole column) then the sorting mixes up the data that should be
together in one row.
In addition to these formal aspects, the logical structure of your table
is very important when using the DataPilot.
Chapter 8 Using the DataPilot 241