If you only put the name of the file (and not the path) in the cell and the
file is open, then J.E.'s solution will work.

Producing the equivalent of

='[Myworkbook.xls]Sheet1'!G26

from
=INDIRECT("'[" & A1 & "]Sheet1'!G26")

if
Myworkbook.xls
alone is found in A1.

--
Regards.
Tom Ogilvy


"Cathy W" wrote in message
...
Tom, have you read all the posts....do you have another suggestion! The
workbook is acutally open. When the template opens, a open dialog appears
for them to select and open the file that they want to take the data
from -
it just minimizes itself as if it wasn't open. Then the name of that file
gets put in cell A1. Then in a formula I would like to say
=[a1]sheet1!g26
and return the value that is in cell G26 of the file path name from
a1...make
sense?

Thanks for your help.

Cathy

"Tom Ogilvy" wrote:

It should return

=K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily Report\[dec
12.05.xls]dos1!g26

If you want It correct, but refreshingly, it won't make any difference
since
Indirect does not work with a closed workbook.

--
Regards,
Tom Ogilvy


"Cathy W" wrote in message
...
Hi. Thanks again for the response. I am going to paste what is put
in
cell
A1 so that you get an idea of what's happening. The formula still
returns
a
#REF! error.

In cell A1, the path of the file the user selects in the open dialog
box
appears as the following:

K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily Report\dec 12.05.xls

So then in cell E12 I want it to say =[a1]dos1!g26 which should return
the
following formula: =[K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily
Report\dec 12.05.xls]dos1!g26 but it's like it's not picking up the
value
in
cell A1.

HELP! :-)

"JE McGimpsey" wrote:

Yup - bad paste on my part:

=INDIRECT("'[" & A1 & "]Sheet1'!G26")

In article ,
"Cathy W" wrote:

Hi. Thanks for the response.

It gives me the error "That name is not valid" and highlights the
"'"
quote
in the formula.


"JE McGimpsey" wrote:

Try

=INDIRECT(["'" & A1 & "]Sheet1'!G26")