Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dottore
 
Posts: n/a
Default 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
  #2   Report Post  
Max
 
Posts: n/a
Default

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


  #3   Report Post  
Max
 
Posts: n/a
Default

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


  #4   Report Post  
Dottore
 
Posts: n/a
Default

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



  #5   Report Post  
Max
 
Posts: n/a
Default

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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--




  #6   Report Post  
Max
 
Posts: n/a
Default

Drop me a note here if you're sending or not.
Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #7   Report Post  
Dottore
 
Posts: n/a
Default

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" wrote:

Drop me a note here if you're sending or not.
Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--



  #8   Report Post  
Max
 
Posts: n/a
Default

"Dottore" wrote:
... 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
table(s). And with a touch of formatting, it could even look pretty neat <g

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


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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #9   Report Post  
Dottore
 
Posts: n/a
Default

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" wrote:

"Dottore" wrote:
... 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
table(s). And with a touch of formatting, it could even look pretty neat <g

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


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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--



  #10   Report Post  
Max
 
Posts: n/a
Default

"Dottore" wrote
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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
Flexible Cell Reference Brandt Excel Discussion (Misc queries) 5 June 2nd 05 10:23 PM
Linking a cell to another workbook cell based on a variable name Brian Excel Discussion (Misc queries) 6 June 1st 05 11:54 PM
How do I use a variable in a cell reference? jsp377 Excel Discussion (Misc queries) 3 January 27th 05 02:43 PM
Cell Reference Math Ralph Howarth Excel Worksheet Functions 0 January 26th 05 07:27 PM


All times are GMT +1. The time now is 01:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"