I'm trying to figure out how to reference the text in a
cell that contains a path to a file to use in a subsequent
formula.

Example:
Assume some file is on server in a directory lets say the
file is called File1.xls

Assume: [File1.xls]!$A$1="Hello World"

In Another workbook...
Let text in A1= "'\\someserver\directory\[File1.xls]'"

Let formula in cell A2= A1$ & "!" & $A$1

Result of formula should be = "Hello World"

I would like to be able to do this without creating a
macro, any help will be greatly appreciated.

Jeff

Thank you, I am familiar with indirect, but I'm looking
for a solution that will work with closed files... any
more ideas???

-----Original Message-----
Hi Jeff,
You can do that using INDIRECT. However it won't work if
File1.xls is not
open.
=Indirect(A1 & "!" & $A$1)


--

John

johnf202 at hotmail dot com


"Jeff Coleman" wrote in message
...
I'm trying to figure out how to reference the text in a
cell that contains a path to a file to use in a
subsequent
formula.

Example:
Assume some file is on server in a directory lets say
the
file is called File1.xls

Assume: [File1.xls]!$A$1="Hello World"

In Another workbook...
Let text in A1= "'\\someserver\directory\[File1.xls]'"

Let formula in cell A2= A1$ & "!" & $A$1

Result of formula should be = "Hello World"

I would like to be able to do this without creating a
macro, any help will be greatly appreciated.

Jeff



.