Scenarios

[Guest]

What can you do with Scenarios? What are they used for.

 

[Debra Dalgleish]

I recently created a workbook that uses scenarios to store information
about a range of product models. The user selects a model from a
dropdown list, and a macro shows the scenario for that model, with all
the parts that are required to build it.

There's a simplified example here:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for Model Pricing Scenario.

 

[Guest]

Thank you for the input. I will look at the worksheet later. Right now I cannot open it because I had to disable my Norton Security 2004 s/w. The dialog box indicates if I open this spreadsheet, macros attached could have viruses. I ran out of harddrive space. Therefore, I need to unpartition my drives, but I need to wait for the software (Partition Magic) to arrive.

Again, Thank you.
Diane

 

[Debra Dalgleish]

If you want to look at the spreadsheet before your AV software is
reinstalled, choose to disable the macros when you open the workbook.
That way, you can see what's in the workbook, but the macros won't be
able to run.

To manually show a scenario, choose Tools>Scenarios. Select a scenario
from the list, and click Show.

 

[Andy B]

Debra

Where is the list kept of which parts make up which model?

Andy.

If you want to look at the spreadsheet before your AV software is
reinstalled, choose to disable the macros when you open the workbook.
That way, you can see what's in the workbook, but the macros won't be
able to run.

To manually show a scenario, choose Tools>Scenarios. Select a scenario
from the list, and click Show.

I cannot open it because I had to disable my Norton Security 2004 s/w. The
dialog box indicates if I open this spreadsheet, macros attached could have
viruses. I ran out of harddrive space. Therefore, I need to unpartition
my drives, but I need to wait for the software (Partition Magic) to arrive.

 

[Debra Dalgleish]

The information is stored in the Scenarios. In a Scenario, you can
specify which cells will change. In my example it's cells B6:C11. To see
the changing cells values for all scenarios, you can create a summary
(Tools>Scenarios, click Summary).

To create a new scenario (i.e. add information about a new model) --

Clear cells B6:C11
Enter the parts and quantities for the new model
Choose Tools>Scenarios
Type a name for the Scenario (e.g. A-03)
Click OK
The current values in the changing cells are shown
Click OK to confirm, click Close
To show any scenario, choose Tools>Scenarios, select a name, click Show
(In my example I have a list of scenarios, which is used as a source
for the data validation dropdown. You could also add the new model
to this list)

You can also program the scenarios. For example, to list the scenarios:
'===================
Sub ScenarioList()
Dim sc As Scenario
Dim wsP As Worksheet
Dim wsM As Worksheet
Dim i As Integer
i = 2
Set wsP = Worksheets("Pricing")
Set wsM = Worksheets("Models")
For Each sc In wsP.Scenarios
wsM.Cells(i, 7).Value = sc.Name
i = i + 1
Next sc
wsM.Range("G2:G" & i - 1).Name = "ModelList"

End Sub
'======================

 

[Andy B]

Debra

Thanks very much. I appreciate your help. I've never used scenarios before,
but having seen how they work I might be able to simplify a number of
spreadsheets.

Andy.

 

[Debra Dalgleish]

Andy,

You're welcome, and I'm sure there are countless other ways to use
scenarios.

I've updated the sample workbook on my site, to include the list
updating code.
http://www.contextures.com/excelfiles.html

Debra