Calc Guide

INDIRECT converts a string to a cell or range
Use INDIRECT to convert a string representation of a cell or range
address to a reference to the cell or range. Table 22 contains examples
accessing data as shown in Table 20.
Table 22. Examples using INDIRECT.
Example Comment
INDIRECT("A2")
Returns cell A2, which contains Bob.
INDIRECT(G1) If Cell G1 contains the text A2, then this
returns Bob.
SUM(INDIRECT("B1:B5")) Returns the sum of the range B1:B5, which
is 194.
INDIRECT(ADDRESS(2; 1)) Returns the contents of cell $A$2, which is
Bob.
OFFSET returns a cell or range offset from another
Use OFFSET to return a cell or range offset by a specified number of
rows and columns from a given reference point. The first argument,
specifies the reference point. The second and third arguments specify
the number of rows and columns to move from the reference point; in
other words, where the new range starts. The OFFSET function has
the following syntax:
OFFSET(reference; rows; columns)
OFFSET(reference; rows; columns; height)
OFFSET(reference; rows; columns; height; width)
Tip
If the width or height is included, the OFFSET function returns a
range. If both the width and height are missing, a cell reference
is returned.
If the height or width are missing, they default to 1. If the height is
present, then a range reference is returned rather than a cell
reference. Using values from Table 12, Listing 19 uses OFFSET to
obtain the quiz scores for the student named Bob.
Listing 19. Complex example of OFFSET.
=SUM(OFFSET(INDIRECT(ADDRESS(MATCH("Bob";A1:A16; 0); 4)); 0; 0; 1; 2))
In its entirety, Listing 19 is complex and difficult to understand. Table
23 isolates each function in Listing 19, providing an easy to understand
explanation of how the example works.
384 OpenOffice.org 3.x Calc Guide