Microsoft Office Tutorials and References
In Depth Information
For example, in Exhibit 7.7 the cloudy option is shown and all others are hidden.
The following are the detailed steps necessary to create a group of 3 options for
which only one option will be displayed:
1. Creating a Group Box —We select the Group Box, the last icon in the Quick
Access Toolbar in Exhibit 7.16. Drag-and-drop the Group Box onto the work-
sheet. See Exhibit 7.17 for an example. Once located, a right click will allow
you to move a Group Box. The grouping of Option Buttons in a Group Box
alerts Excel that any Option Buttons placed in the box will be connected or asso-
ciated with each other. Thus, by placing three buttons in the box, each button will
be assigned a specific output value (1, 2, or 3), and those values can be assigned
to a cell of your choice on the worksheet. You can then use this value in a logical
function to indicate the option selected. (If four buttons are used then the values
will be 1, 2, 3, and 4.)
2. Creating Option Buttons—Drag-and-drop the Option Button found in the Quick
Access Toolbar into the Group Box. When you click on the Option Button and
move your cursor to the worksheet, a cross will appear. Left click your cursor and
drag the box that appears into the size you desire. This box becomes an Option
Button. Repeat the process in the Group Box for the number of buttons needed.
A right click will allow you to reposition the button and text can be added to
identify the button.
3. Connecting button output to functions—Now we must assign a location to the
buttons that will indicate which of the buttons is selected. Remember, that only
one button can be selected at a time. Place the cursor on any button and right
click. A menu will appear and the submenu of interest is Format Control. See
Exhibit 7.17. Select the submenu and then select the Control tab. At the bottom
you will see a dialogue box requesting a Cell Link . In this box place the cell
location where the buttons will record their unique identifier to indicate the single
button that is selected. In this example D22 is the cell chosen, and by choosing
this location for one button, all grouped buttons are assigned the same Cell Link.
Now the cell can be used to perform worksheet tasks.
4. Using the Cell Link value—In Fr. Efia’s spreadsheet model, the Cell Link values
are used to display or hide calculations. For example, in Exhibit 7.18 cell E21,
the Attendance for the Cloudy scenario, contains:
2, C6,0). Cell C6
is currently set to 2500. This logical function examines the value of B29, the Cell
Link that has been identified in step 3. If B29 is equal to 2, it returns 2500 as the
value cell E21. If it is not equal to 2, then a zero is inserted. A similar calculation
is performed for the Entry Fee in cell G21 by using the following cell function:
=
=
IF(B29
=
0,0,C3 E21). In this case, cell E21, the Attendance for Cloudy, is
examined and if it is found to be zero, then a zero is inserted in E21. If it is not
zero, then a calculation is performed to determine the Entry Fee revenue (2,500
30
IF(E21
=
75,000). The Revenues of Events are calculated in a similar manner. Note
that it also is possible to show all values for all scenarios (Sunshine, Cloudy, and
Rainy) and eliminate the logical aspect of the cell functions, and then the Option
Buttons would not be needed. The buttons allow us to focus strictly on a single
=
Search JabSto ::




Custom Search