Input cell reference is not valid (One Variable Data Table)

At Debra's suggestion I am posting my message a second time with a bit more
detail :

I have put together a financial budget for 15 years and I now wish to
calculate the incidence of the change in the debt ratio on the ROE (Return on
Equity) of the project.

The debt ratio is a value given in one cell (C47) on Sheet 1 for the first
year and this same value is than copied to the other 14 years to the right

The ROE of the project is calculated in Sheet 2 after going through a series
of formulas in between, which calculate the cash flows of the project over
the 15 years

In Sheet 3 I put the input cell in F5 with the following formula =
Sheet1!C47 to indicate this is the cell I want to change to see the effect on
the ROE

I put the various debt ratios in a column in Sheet 3 (B3:B10) and copy the
formula for the ROE in cell C2 referring to the proper range of cash flows in
sheet 2

Now selecting the cell range B2:C10 and click data table referring to cell
F5 on the same sheet 3 as the column input cell should spill out in C3:C10
the different ROEs under the B3:B10 scenarios ...

For reasons I can't fathom I get all the same values in cell C3:C10 which is
the correct value of the ROE but only if the debt ratio is set at the value
in cell C47 on Sheet 1. If I try to put Sheet1!C47 as the input cell in the
Data Table command, I get an Input cell reference is not valid message
displayed.

What am I doing wrong ??? (Calculation is of course set to Automatic)

Appreciate

"Dottore" wrote:
....
> .. If I try to put Sheet1!C47 as the input cell in the
> Data Table command, I get an Input cell reference
> is not valid message displayed.

Believe this is a limitation of the Data Table, which seems to require the
row / column input cells to be on the same sheet as the table

Experiment on a spare copy of your file. Try cut and paste C47 from Sheet1
to somewhere on Sheet 3 where you have the data table set-up (e.g.: cut C47
from Sheet1 and paste into say, Sheet3's E1). Then try the Data > Table
command again on B2:C10, pointing now to E1 as the column input cell.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"Dottore" wrote:
....
> .. If I try to put Sheet1!C47 as the input cell in the
> Data Table command, I get an Input cell reference
> is not valid message displayed.

Believe this is a limitation of the Data Table, which seems to require the
row / column input cells to be on the same sheet as the table

Experiment on a spare copy of your file. Try cut and paste C47 from Sheet1
to somewhere on Sheet 3 where you have the data table set-up (e.g.: cut C47
from Sheet1 and paste into say, Sheet3's E1). Then try the Data > Table
command again on B2:C10, pointing now to E1 as the column input cell.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"Dottore" wrote:
....
> .. If I try to put Sheet1!C47 as the input cell in the
> Data Table command, I get an Input cell reference
> is not valid message displayed.

Believe this is a limitation of the Data Table, which seems to require the
row / column input cells to be on the same sheet as the table

Experiment on a spare copy of your file. Try cut and paste C47 from Sheet1
to somewhere on Sheet 3 where you have the data table set-up (e.g.: cut C47
from Sheet1 and paste into say, Sheet3's E1). Then try the Data > Table
command again on B2:C10, pointing now to E1 as the column input cell.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Apologies, pl disregard the earlier views. Think you've covered that point
in your post. Perhaps what you need is a 2 variable Data Table set-up ? I'm
just guessing here ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks Max for looking at the pbm

Your first suggestion would not work as in this case you put a fixed value
in Sheet3 E1 and the function would not know which value in the earlier
formulas to replace. This is why I refer to Sheet1!C47 in cell F5 on Sheet3
through a formula.

As I am looking at the incidence of only one variable (the debt ration in my
example) I believe a one variable data table should do the trick

I am sure the solution must be frustratingly simple but I just can't put my
finger on the problem ...

Dottore

"Max" wrote:

> Apologies, pl disregard the earlier views. Think you've covered that point
> in your post. Perhaps what you need is a 2 variable Data Table set-up ? I'm
> just guessing here ..
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

Thanks Max for looking at the pbm

Your first suggestion would not work as in this case you put a fixed value
in Sheet3 E1 and the function would not know which value in the earlier
formulas to replace. This is why I refer to Sheet1!C47 in cell F5 on Sheet3
through a formula.

As I am looking at the incidence of only one variable (the debt ration in my
example) I believe a one variable data table should do the trick

I am sure the solution must be frustratingly simple but I just can't put my
finger on the problem ...

Dottore

"Max" wrote:

> Apologies, pl disregard the earlier views. Think you've covered that point
> in your post. Perhaps what you need is a 2 variable Data Table set-up ? I'm
> just guessing here ..
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

Thanks Max for looking at the pbm

Your first suggestion would not work as in this case you put a fixed value
in Sheet3 E1 and the function would not know which value in the earlier
formulas to replace. This is why I refer to Sheet1!C47 in cell F5 on Sheet3
through a formula.

As I am looking at the incidence of only one variable (the debt ration in my
example) I believe a one variable data table should do the trick

I am sure the solution must be frustratingly simple but I just can't put my
finger on the problem ...

Dottore

"Max" wrote:

> Apologies, pl disregard the earlier views. Think you've covered that point
> in your post. Perhaps what you need is a 2 variable Data Table set-up ? I'm
> just guessing here ..
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

Thanks Max for looking at the pbm

Your first suggestion would not work as in this case you put a fixed value
in Sheet3 E1 and the function would not know which value in the earlier
formulas to replace. This is why I refer to Sheet1!C47 in cell F5 on Sheet3
through a formula.

As I am looking at the incidence of only one variable (the debt ration in my
example) I believe a one variable data table should do the trick

I am sure the solution must be frustratingly simple but I just can't put my
finger on the problem ...

Dottore

"Max" wrote:

> Apologies, pl disregard the earlier views. Think you've covered that point
> in your post. Perhaps what you need is a 2 variable Data Table set-up ? I'm
> just guessing here ..
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

Thanks Max for looking at the pbm

Your first suggestion would not work as in this case you put a fixed value
in Sheet3 E1 and the function would not know which value in the earlier
formulas to replace. This is why I refer to Sheet1!C47 in cell F5 on Sheet3
through a formula.

As I am looking at the incidence of only one variable (the debt ration in my
example) I believe a one variable data table should do the trick

I am sure the solution must be frustratingly simple but I just can't put my
finger on the problem ...

Dottore

"Max" wrote:

> Apologies, pl disregard the earlier views. Think you've covered that point
> in your post. Perhaps what you need is a 2 variable Data Table set-up ? I'm
> just guessing here ..
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

Apologies, pl disregard the earlier views. Think you've covered that point
in your post. Perhaps what you need is a 2 variable Data Table set-up ? I'm
just guessing here ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Apologies, pl disregard the earlier views. Think you've covered that point
in your post. Perhaps what you need is a 2 variable Data Table set-up ? I'm
just guessing here ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Apologies, pl disregard the earlier views. Think you've covered that point
in your post. Perhaps what you need is a 2 variable Data Table set-up ? I'm
just guessing here ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Apologies, pl disregard the earlier views. Think you've covered that point
in your post. Perhaps what you need is a 2 variable Data Table set-up ? I'm
just guessing here ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"Dottore" wrote:
....
> .. If I try to put Sheet1!C47 as the input cell in the
> Data Table command, I get an Input cell reference
> is not valid message displayed.

Believe this is a limitation of the Data Table, which seems to require the
row / column input cells to be on the same sheet as the table

Experiment on a spare copy of your file. Try cut and paste C47 from Sheet1
to somewhere on Sheet 3 where you have the data table set-up (e.g.: cut C47
from Sheet1 and paste into say, Sheet3's E1). Then try the Data > Table
command again on B2:C10, pointing now to E1 as the column input cell.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"Dottore" wrote:
....
> .. If I try to put Sheet1!C47 as the input cell in the
> Data Table command, I get an Input cell reference
> is not valid message displayed.

Believe this is a limitation of the Data Table, which seems to require the
row / column input cells to be on the same sheet as the table

Experiment on a spare copy of your file. Try cut and paste C47 from Sheet1
to somewhere on Sheet 3 where you have the data table set-up (e.g.: cut C47
from Sheet1 and paste into say, Sheet3's E1). Then try the Data > Table
command again on B2:C10, pointing now to E1 as the column input cell.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--