Jim said:
Using Office 2000, Scenarios looks like a great function
except ...
- how can I force it to over-right the existing
sheet 'name' instead of creating a new 'name(2)' sheet
each time I run the Scenario function?
How are you creating new worksheets using Scenarios?
- how can I force Scenario to use cell values instead of
entering values manually via the wizard?
You could use VBA to add or edit scenarios, and assign ranges' values to the
scenario's .Values property. Without VBA, not possible AFAIK.
Generally you're better off avoiding Excel's Scenarios. They do very little
for you unless you also use Excel's Report Manager, in which case Views and
Scenarios can be combined to do some useful things. On its own, Scenarios
could be replaced with indirect references, e.g., the key formula referring
to a range named GrowthRate, and the cell GrowthRate containing a formula
like
=VLOOKUP(GrowthRateScenario,GrowthRateTable,2,0)
where GrowthRateTable contains 2 columns and multiple rows with the first
column containing scenario names and the second column the corresponding
growth rate figures. GrowthRateScenario would be either a cell or defined
name referring to a text constant giving the name of the scenario you want.
If it's a cell, you could use the first column of GrowthRateTable as a data
validation drop-down list.
This would give you nearly as much user-friendliness as Scenarios with much
more potential flexibility since the cells in the second column of
GrowthRateTable could be formulas or constants.
