Microsoft Office Tutorials and References
In Depth Information
Adding, editing, and deleting scenarios
workbooks containing the scenarios you want, open the worksheet where you want the
result to go, and click Merge in the Scenario Manager dialog box. When you do, a dialog
box like the one in Figure 18-13 appears.
Figure 18-13 Clicking Merge in the Scenario Manager dialog box displays the Merge Scenarios
dialog box, with which you can import scenarios from any worksheet in any open workbook.
INSIDE OUT Request only the data you need
Merging scenarios works best if the basic structure of all the worksheets is identical.
Although this uniformity isn’t a strict requirement, merging scenarios from worksheets
that are laid out differently can cause changing cell values to appear in unexpected
locations. For this reason, and because it’s generally difficult to ascertain the skill level
of everyone contributing data, you might try a different approach. Distribute a
questionnaire requesting only the data you need, use external cell references to link the
requested data with the appropriate locations on your master worksheet, and create
the scenarios yourself.
In the Merge Scenarios dialog box, you select the workbook and worksheet from which you
want to merge scenarios. As shown in Figure 18-13, if you select a worksheet in the Sheet
list, a message at the bottom of the dialog box tells you how many scenarios exist on that
worksheet. When you click OK, the scenarios on that worksheet are copied to the active
worksheet. After merging all the scenarios from your coworkers, the Scenario Manager
dialog box for this example looks like the one shown in Figure 18-14.
Notice in Figure 18-14 that the Comment box displays the name of the creator and
modifier of the selected scenario. If the Scenarios list includes similarly named scenarios, Excel
appends a creator name, date, or number to the name. You can use the Edit button to
rename the scenarios if you want.