referring to a cell containing a file name

[canadiangal]

Can you tell me if this is possible?

Instead of putting the file name in the original cell, I want to
reference it.
For example, cell B10 contains "ABC.xls"

But in A1 I want to refer to it like this:
=SUM([B10]Sheet5!A5:A10)

Do I need to add quotation marks or anything? It doesn't seem to like
this...

Thanks

 

[Guest]

Use INDIRECT() to splice cell contents:


=SUM(INDIRECT("[" & B10 & "]Sheet5!$a$5:$a$10"))

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Can you tell me if this is possible?

Instead of putting the file name in the original cell, I want to
reference it.
For example, cell B10 contains "ABC.xls"

But in A1 I want to refer to it like this:
=SUM([B10]Sheet5!A5:A10)

Do I need to add quotation marks or anything? It doesn't seem to like
this...

Thanks