MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Data-Table & sensitivity analysis (pls. help - 3rd post)

Posted by Morpheus on October 23, 2001 11:52 AM

Trying to do a sensitivity analysis using Data Table function. Is there any way that one can input cells in the 'Row/Column' inputs, from different worksheets within the same workbook. Every time I try to input a cell from a different worksheet in the Row/Column menu, I get an error/invalid command.

Posted by Mark W. on October 23, 2001 1:12 PM

I for one never understand your question. By
"'Row/Column' inputs" do you mean the row and column
input cell references entered on the Table dialog?
If so, why would you need to have them on a separate
worksheet? The row and column input cells are
used as work areas that Excel utilizes to compute
the table results.

Let's look at an example of how these input
references are used. Suppose that the row input
cell is A1 and the column input is B1. The data
table formula in cell A4 is =A1*B1^2. The values
{10;20;30} are entered into cells A5:A7. The values
{5,15,25} are entered into cells B4:D4. The resultant
table values are {500,1500,2500;2000,6000,10000;4500,13500,22500}.

Excel loads the 1st column value, 10, into cell B1 and
the 1st row value, 5, into cell A1. The table
formula is evaluated producing 500 (5*10^2). The entire
table is resolved in the fashion.

Now, why do you need this "work area" to reside
on another worksheet?