How do I create a dynamic scenario in Excel?

View Scenario - MacroTo make it easier to switch between Scenarios, you can create a drop down list of Scenarios, and use event code to show the selected Scenario.In the sections b

How do I create a dynamic scenario in Excel?

View Scenario - Macro

To make it easier to switch between Scenarios, you can create a drop down list of Scenarios, and use event code to show the selected Scenario.

In the sections below, there are instructions for the four steps:

  1. Create a list of scenario names
  2. Name that list
  3. Create drop down list on worksheet, using data validation
  4. Add a small amount of code, to automate the scenario selection

Step 1: Create a list of Scenarios

Follow these steps to add a new worksheet, and make a list of Scenarios

  1. In the row of sheet tabs, click the + button (New sheet), to add a new worksheet
  2. Double-click on the new sheet's tab, and rename it as Lists
  3. In cell A1, type a heading for the list - Scenarios
  4. Make the heading cell Bold font
  5. In cells A2 and A3, type the Scenario names - Finance and Marketing
Scenario list

Step 2) Name list of Scenarios

The list will be easier to maintain if it's a dynamic named range. With this type of named range, it will adjust automatically if you add or remove Scenario names.

To create the named range follow these steps:

  1. On the Lists worksheet, select cell A1
  2. On the Excel Ribbon, click the Formula tab
  3. In the New Name dialog box, type this one-word name for the list:
  4. In the Scope drop down, select Workbook
  5. The Comment box can be left blank
  6. For Refers to, type the following formula:
  • =OFFSET(Lists!$A$1,1,0, COUNTA(Lists!$A:$A)-1,1)

5. Click OK

dynamic named range for Scenario list

Video liên quan