Macro as alternative to limitations of data table...

[Tripp Knightly]

Has anybody seen any sample macros that do basically what a data table
does to avoid the requirement of needing the input sensitivity
variable(s) being on the same sheet as the data table?

Am thinking it should be fairly straightforward to have a macro
iterate down a column of scenario values, change the input variable,
copy result until end of column. But alas my VB skills, for now, are
sub-novice.

Thoughts?

 

[Mark]

Hi,
Macros can do just about anything you want.
If you do not get a reasonable respose from this group then
send me a sample sheet of your work and I will try to
write a macro for it.

Regards Mark
(e-mail address removed)

HTTP://au.geocities.com/excelmarksway

 

[Tripp Knightly]

Thanks, ok, so suppose we're trying to mimic a simple 1 variable data
table.

Left column is price (a cell in another sheet is actually named
"Price"), and single result column has a formula above first row with
the corresponding revenue. Macro should iterate / loop to change
price, copy resulting new revenue to relevant row, then change price
back to original value.

Col1 Col2
Price "=price*units"
$1 to be computed
$2 to be computed
$3 to be computed
$4 to be computed
$5 to be computed