Search Contextures Sites
Custom Search

Excel Scenarios -- Automatically Show Scenario

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.

Create a Dropdown List of Excel Scenarios  

Add the Event Code  

Test the Code  

Download the sample File

 

Create a Dropdown List of Excel Scenarios

This example will use the Excel Scenarios created here:
   Excel Scenarios -- Create and Show  

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

  1. On the Insert menu, choose Worksheet
  2. Name the new sheet as Lists  
  3. Starting in cell A1, type the heading, and Scenario names, as shown below. 

Name the list of Scenarios

The list will be easier to maintain if it's a dynamic named range.
To create the named range:

  1. On the Lists worksheet, select cell A1
  2. Choose Insert | Name | Define
  3. For Names in Workbook, type: ScenarioList
  4. For Refers to, type the following formula:

=OFFSET(Lists!$A$1,1,0,COUNTA(Lists!$A:$A)-1,1)

  5. Click OK

Create the dropdown list

  1. On the Budget worksheet, select cell B1
  2. Choose Data | Validation
  3. From the Allow dropdown, choose List
  4. In the Source box, type:    =ScenarioList 
  5. Click OK     

Add the Event Code

  1. Right-click on the Budget sheet tab
  2. Choose View Code
  3. From the Object dropdown, at the top left, choose Worksheet.

  4. From the Procedure dropdown, at the top right, choose Change.

  5. Where the cursor is flashing, enter the following code:
  If Target.Address = "$B$1" Then
    ActiveSheet.Scenarios(Target.Value).Show
  End If
  1. Choose File | Close and Return to Microsoft Excel 

Test the Code

  1. On the Budget worksheet, select an item from the dropdown list in cell B1.
  2. That Scenario will be displayed.
  3. Close and save the workbook.

Note: When opening the workbook, you will have to enable macros in order to use this code.

Download the Sample File

Download the zipped sample Excel Scenarios completed file  

More Tutorials

1. Excel Scenarios -- Create and Show  
2. Excel Scenarios -- Scenario Summaries  
3. Excel Scenarios -- Automatically Show Scenario  
4. Excel Scenarios -- Programming  

 

 

More Tutorials

Create and Show Scenarios

Scenario Summaries  
 
Scenarios - Programming  

 

 

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: December 27, 2015 12:47 AM