Page 215 - Excel Data Analysis

11 537547 Ch10.qxd 3/4/03 12:03 PM Page 201

FORM CONTROLS 10

When working with a value selected from a ListBox or
ComboBox control, you typically want to use that selection to
determine another cell value. For example, assume that you
have the following Excel list in cells H2:I4.
Example:
Computer $1295
Monitor $995
Keyboard $55
You can use the Index function to determine the price based
on the equipment selection. For example, if you select Monitor
from the control, Excel places a value of 2 in the linked cell. If
you want to find the cost for the selection, you type a formula
similar to the following, assuming that C2 is the linked cell:
Example:
=INDEX($H$2:$1$4, C2, 2)
The Index function actually creates an array of the Excel list
and uses the selection from the control to determine which
element of the array to return. The function actually uses three
different arguments:
Example:
Array, Row_num, and Column_num.

Keyboard

‡ Select the desired control ■ Excel places a numeric
value. value representing the control
selection in the linked cell.

201