Performing a Sensitive Analysis Not calculating

Place the Data Table’s row and column input cells on the same worksheet as the sensitivity table; selecting an input cell on another sheet commonly produces “Input cell reference is not valid.” ^1^

Correct the input-cell error

If the transaction model’s assumptions are currently on another worksheet:

1. On the worksheet containing the sensitivity table, create one local input cell for each variable—for example:
   • B3: interest rate
   • B4: exit cap rate
2. Change the model so its calculations refer to these local input cells. The model’s formulas and output may remain on other worksheets.
3. In the sensitivity table’s upper-left corner, enter a normal formula referencing the model output, for example:

      ='Returns Model'!$H$25
   

4. Select the entire sensitivity range, including:
   • The output formula in the upper-left corner
   • The row and/or column assumption values
   • The blank result area
5. Select Data > What-If Analysis > Data Table.
6. Enter direct, single-cell references from the current worksheet:
   • Row input cell: the model input represented by values across the top row
   • Column input cell: the model input represented by values down the first column
7. Select OK.

Do not enter a range, multiple cells, or a reference such as 'Assumptions'!B3 in either input box.

For a two-variable table, the upper-left formula must ultimately depend on two different input cells. Excel substitutes each row-and-column combination into those cells and records the formula result. ^2^

If every result is still identical

Since the cell links have already been checked, test these two specific conditions:

1. Verify each input independently
   • Manually change the local row input cell and confirm the output formula changes.
   • Restore it, then manually change the local column input cell and confirm the output changes.
   • If either change has no effect, that variable is not in the output formula’s dependency path. Correct the model reference before rebuilding the Data Table.
2. Enable Data Table recalculation
   • Go to File > Options > Formulas.
   • Under Calculation options, select Automatic.
   • Press Ctrl+Alt+F9 to force a full workbook recalculation.

If calculation is set to Automatic Except for Data Tables, ordinary formulas recalculate while sensitivity tables may remain unchanged until manually recalculated. Pressing F9 recalculates pending Data Tables; Ctrl+Alt+F9 forces calculation across all open workbooks. ^3^

Also confirm that the row and column variables have not been reversed: values across the top correspond to Row input cell, while values running downward correspond to Column input cell.

References

1. Excel 2007/ In put Cell reference is not valid While using What If Analysis
2. Calculate multiple results by using a data table
3. Excel Recalculation