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

[Guest]

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

 

[Max]

:
....

.. 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.

 

[Max]

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 ..

 

[Guest]

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]

If you like, email a zipped copy of your file over to me at:
demechanik < at > yahoo < dot > com

I'll take a look at your set-up ..

 

[Max]

Drop me a note here if you're sending or not.
Thanks.

 

[Guest]

Sorry Max, was out for lunch

First of all I solved the problem by putting all the data and formulas in
one single sheet. This is not convenient though and certainly does not eye
very well. It does explain however the input cell reference is not valid
message as the input reference cell is no longer on a different sheet.
Strange though that making a reference in Sheet3 to the variable in Sheet1
does not solve this problem.

I could send you a copy of the file if you wish, but better even could let
you have access to my computer via GoToMyPC. If you have Skype as well, we
could even voice communicate at the same time. The file is a lot larger than
it sounds in this message board and you would be spending too much time if
you were to look at it on your own I am afraid.

So let me know what suits you best and thanks for helping me out on this one

 

[Max]

... First of all I solved the problem by putting all the data
and formulas in one single sheet. This is not convenient though
and certainly does not eye very well.

Glad to hear you got it working. I'd usually do this (i.e. put everything in
a single sheet) for data tables and get it all working properly. Then, if
required, "re-present" the data table (or part of it) elsewhere on other
sheets via simple link formulas pointing to the sheet with the data

.. So let me know what suits you best ..

As you've got it working, there's no need to send the file ..

 

[Guest]

OK Max I have allready started restructuring the work book as per your
suggestion. Still I find it odd it does not work if the input cell reference
is outside the active sheet. Should I write to MS about it ? Never done
this before and still feel there is somehow a way around it

Poka (Russian for bye) and thanks once more

 

[Max]

OK Max I have allready started restructuring the work book as per your
suggestion. Still I find it odd it does not work if the input cell reference
is outside the active sheet. Should I write to MS about it ? Never done
this before and still feel there is somehow a way around it

I don't know. Its your choice if you want to <g>.
But I'd just work within the limitations of the Data Table as it stands.
(It's not that bad to me)

Poka (Russian for bye) and thanks once more

You're welcome !
Thanks for the exchange ..