indirect

[WoodyHays]

I need some help, I must have tried this a hundred ways and can't seem to get the syntax correct.

This is the result I want, where Y is comming from cell C10.
='[FP Calculator.xls]CashFlow'! y 20

I think I need the =indirect formula, something like this, but I can't get it correct.
='[FP Calculator.xls]CashFlow'! &indirect(c10)& 20

Can anyone straighten me out on this?

Thanks,
Woody Hays

 

[phantom1975]

Try this instead:

=INDIRECT("'[FP Calculator.xls]CashFlow'!" & C10 & "20")

 

[WoodyHays]

Thanks,
It works great as long as the source file is open. When the source is closed, it gives an error.

I need for users to be able to use this when the source is closed.

The reference for the linked file is within the indirect function, which I think requires it to be open. can just the local cell erference be in the indirect? or can something else like index be used?

I am really clueless, jsust guessing out loud.

Woody Hays

 

[phantom1975]

From Excel Help on INDIRECT:

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

 

[WoodyHays]

Thanks,
I guess i get to spend several hours tonight manually adjusting a lot of formulas.

Woody Hays

 

[phantom1975]

There might be a faster approach to your problem. Describe what you need to do and a solution might be available.

 

[WoodyHays]

I have 50 columns of horizontal data, each column = a year.

I need to include them in another report that needs them vertically, 50 years.

46 rows of 50 columns in the source need to be linked to 46 columns of 50 rows in the target. I can't use transpose because that is an aray which the users can't edit if necessary.

I was trying to set up a column in the target to represent each column address in the source. Say column C would read down x,y,z etc. Then I wanted to modify the linked formulas in row 1 to read the address reference from column C. That way I could just copy them all down 50 years and they wouldn't get messed up.

I bet this is hard to follow.

I appreciate your thoughts.
Woody Hays