Last updated: February 7, 2010 10:28 PM
Search Contextures Sites
You can use Excel Scenarios to store several versions of the data in a worksheet. To make them easier to use, you can use programming to automatically show the selected Scenario.
This example will use the Excel Scenarios created here:
Excel Scenarios -- Create and Show
Download the zipped sample Excel Scenarios starter file
To view a Scenario, you can choose Tools | Scenarios, then select a Scenario, click the Show button, and close the Scenario Manager dialog box. To make it easier to switch between Scenarios, you can create a dropdown list of Scenarios, and use event code to show the selected Scenario.
Note: This example will only work in Excel 2000, or later versions.
Create a list of Scenarios
- On the Insert menu, choose Worksheet
- Name the new sheet as Lists
- Starting in cell A1, type the heading, and Scenario names, as shown at right.
Name the list of Scenarios
The list will be easier to maintain if it's a dynamic named range.
To create the named range:
- On the Lists worksheet, select cell A1
- Choose Insert | Name | Define
- For Names in Workbook, type: ScenarioList
- For Refers to, type the following formula:
5. Click OK
Create the dropdown list
- On the Budget worksheet, select cell B1
- Choose Data | Validation
- From the Allow dropdown, choose List
- In the Source box, type: =ScenarioList
- Click OK
- Right-click on the Budget sheet tab
- Choose View Code
- From the Object dropdown, at the top left, choose Worksheet.
- From the Procedure dropdown, at the top right, choose Change.
- Where the cursor is flashing, enter the following code:If Target.Address = "$B$1" Then ActiveSheet.Scenarios(Target.Value).Show End If
- Choose File | Close and Return to Microsoft Excel
- On the Budget worksheet, select an item from the dropdown list in cell B1.
- That Scenario will be displayed.
- Close and save the workbook.
Note: When opening the workbook, you will have to enable macros in order to use this code.
Contextures Inc., Copyright ©2012
All rights reserved.