Datasheet

23
Chapter 1: Using Macros and Form Controls
8
8
When working with a value selected from a list box or combo box control, you may want to use the value in the
linked cell 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)
When the user selects Monitor, the INDEX function returns $995. 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. The function uses
three arguments:
Array, Row_num, and Column_num. See the file Form Control Example.xlsm, which you can
download from the Web site for this book to see an example.
•
Excel selects the value and places a
numeric value representing the
control selection in the linked cell.
8 Click the down arrow and then
select the desired control value.
03_591598-ch01.indd 2303_591598-ch01.indd 23 6/11/10 1:41 PM6/11/10 1:41 PM