Data Table reference cells in a different tab

Texas Longhorn

Active Member
Joined
Sep 30, 2003
Messages
492
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Active Member
Joined
Sep 30, 2003
Messages
492
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Active Member
Joined
Sep 30, 2003
Messages
492
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,843
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top