Data Table: Input Parameters in Different Sheet from the Table

Is there a way to have the Row Input and Column Input parameters for a Data Table be in a different sheet than the Data Table?

When I have them in a different sheet I get an 'Input cell reference not valid' error.

Thanks in advance

Excel Facts

Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Col Delane

Active Member

I too am suffering from this problem.

I read another post that referred to Data Tables in xl2003 (?) that referenced a Microsoft Knowledgebase article which suggested a work-around of including a text string in the row/column input cells that had the address of the cell on the other sheet. However, I tried this and it didn't work for me.

Other posts I've read seem to indicate that these special cells MUST be on the same sheet as the Data Table - which IMHO is just plain stupid. Why would MS design such great WhatIf functionality as Data Tables (that will typically be utilised by advanced users, financial modellers, etc. who build multi-sheet workbooks) and NOT allow the row and column input cells to be on another sheet? How short-sighted!