Datasheet

Chapter 1: Using Macros and Form Controls
23
8
Monitor
8 Select the desired control value.
•
Excel places a numeric value representing the
control selection in the linked cell.
When working with a value selected from a list box or combo box control, you may want to use that selection
to set the value of another cell. For example, assume 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 the user selects Monitor from the control, Excel places a value of 2 in the linked cell. If you want
users to find the cost of 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 control selection to determine which
element in the array to return, in this case the price, The function uses three arguments: Array, Row_num,
and Column_num.
04_132302 ch01.qxp 7/27/07 9:20 PM Page 23