Plotting scenarios

[Jack Schitt]

Fairly complex multi-sheet workbook contains a precedent cell and a
dependent cell. All sorts of convoluted computations are undertaken to
arrive at the value in the dependent cell.
I wish to plot a graph of dependent value v precedent value. At the moment
the only way I can think of doing this is to set up a separate table,
manually vary the precedent cell by constant increments and manually note in
the table the value of the dependent cell, then to use that table as the
data for a graph. I feel that there must be a better way that takes out the
drudgery. Help??

Thanks.

 

[Jack Schitt]

I suppose I could use dynamic named ranges to embrace the table that will
form the data for the graph, and then use VBA to populate the table by
sequentially varying the value entered in the precedent cell, recalculate
the workbook, enter the values of the precedent and dependent cells in a new
row the data table, recalculate again, and continue cycling through. Was
looking for a non-VBA solution but I think this should work.