Data Table reference cells in a different tab

[Texas Longhorn]

Hello all,

I'm trying to set up a data table to do a sensitivty analysis for a share price valuation. It's a two-variable table, with WACC and terminal growth rate as my two variables. These two variables are in an "Assumptions" tab, and I'm trying to do the sensitivity analysis in my "Valuation" tab. I'm getting "input cell not valid" errors, and I'm wondering if Excel will only allow me to build tables using cell references in the same sheet. Any help is appreciated.

thanks,

Bill

 

[Joe Was]

You need a 3D reference.

on sheet2 put:

=Sheet1!Sum(A1:A33)

this gets the sum of A1:A33 from sheet1 and adds it ti the cell on sheet2 that contains the formula.

 

[Texas Longhorn]

Joe,

Thanks for the reply. I'm not sure I am following though...my two variables are WACC and terminal growth, each of which are in single cells. These two percentages flow into my valuation tab, where they're used to compute several cells that end up as a per share value. The per share value is the value I'm doing the analysis on.

Thanks,

Bill

 

[Joe Was]

You can use a 3D reference in any formula that takes a cell reference.

So where ever you add your cells addresses use a 3D in place of a single reference.

 

[Texas Longhorn]

Thanks Joe...I'll give it a shot!

 

[Texas Longhorn]

Still no luck on this. I think I may not be describing my issue well - here goes: On tab "B" I have a two-variable data table that I created by using Data, Table. I have a row input cell and a column input cell; these are currently also in tab "B". Done this way, everything works fine.

However, I'd like to have my row and column input cells on tab "A". Unfortunately, when I try this, I get the error, "Input Cell Reference Is Not Valid." I currently have a work-around in place where in tab "B" I reference the cells in tab "A", and then reference the referenced cells in tab "B" when making my table. However, this seems silly. Is there another way?

Thanks!