indirect

WoodyHays

Board Regular
Joined
May 14, 2002
Messages
185
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
Try this instead:

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

WoodyHays

Board Regular
Joined
May 14, 2002
Messages
185
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

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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

Board Regular
Joined
May 14, 2002
Messages
185

ADVERTISEMENT

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

Woody Hays
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
There might be a faster approach to your problem. Describe what you need to do and a solution might be available.
 

WoodyHays

Board Regular
Joined
May 14, 2002
Messages
185
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

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