Calc Guide

The formula =VLOOKUP(83; $Sheet2.$A$2:$B$7; 2) is an obvious

solution. Dollar signs are used so that the formula can be copied and

pasted to a different location and it will still reference the same values

in Table 20.

ADDRESS returns a string with a cell’s address

Use ADDRESS to return a text representation of a cell address based

on the row, column, and sheet; ADDRESS is frequently used with

MATCH. The supported forms for ADDRESS are as follows:

ADDRESS(row; column)

ADDRESS(row; column; abs)

ADDRESS(row; column; abs; sheet)

The row and column are integer values where ADDRESS(1; 1) returns

$A$1. The abs argument specifies which portion is considered

absolute and which portion is considered relative (see Table 21); an

absolute address is specified using the $ character. The sheet is

included as part of the address only if the sheet argument is used. The

sheet argument is treated as a string. Using

ADDRESS(MATCH("Bob";A1:A5 ; 0); 2) with the data in Table 20

returns $B$2.

Tip

Calc supports numerous powerful functions that are not

discussed here. For example, the ROW, COLUMN, ROWS, and

COLUMNS statements are not discussed; a curious person would

investigate these functions.

Table 21. Values supported by the abs argument to ADDRESS.

Value Description

1 Use absolute addressing. This is the default value if the argument

is missing or an invalid value is used. ADDRESS(2; 5; 1) returns

$E$2.

2 Use an absolute row reference and a relative column reference.

ADDRESS(2; 5; 2; "Blah") returns Blah.E$2.

Use a relative row reference and an absolute column reference.

ADDRESS(2; 5; 3) returns $E2.

4 Use relative addressing. ADDRESS(2; 5; 4) returns E2.

Chapter 13 Calc as a Simple Database 383