building an external link based on a cell value

J

Jason

I would like to build an external link in a spreadsheet
that automatically changes based on a cell value.

For example, I have the following link:

=SUMIF('[Location Income Statements 2004.xls]Mar04'!
$14:$14,P5,'[Location Income Statements 2004.xls]Mar04'!
$18:$18)

Instead of Mar04 in the formula above, I would like to
use a cell's contents.... cell A8 for example.

Is this possible?

Thanks,

Jason
 
D

Dick Kusleika

Jason

You can use the INDIRECT function.

=SUMIF(INDIRECT("'[Location Income Statements 2004.xls]" & A8 &
"'!$14:$14"),P5...

Indirect won't work on closed workbooks, so it's not suitable for every
situation. If you need to work on closed workbooks, you'll either need to
hardcode the link or use a macro to change the formula whenever A8 changes.
 
H

Harlan Grove

You can use the INDIRECT function.

=SUMIF(INDIRECT("'[Location Income Statements 2004.xls]" & A8 &
"'!$14:$14"),P5...

Indirect won't work on closed workbooks, so it's not suitable for every
situation. If you need to work on closed workbooks, you'll either need to
hardcode the link or use a macro to change the formula whenever A8 changes.
Click to expand...
...

Nope. There's a udf approach. See #4 in

http://www.google.com/[email protected]
 
Top