Since the file will be open, there's no need for the path (and
INDIRECT() doesn't work with closed workbooks), so I'd suggest modifying
the Workbook_Open code to just put the workbook name in.

If you can't do that, then you can use something like:

=INDIRECT("'[" & MID(A1, FIND("$$", SUBSTITUTE(A1, "\", "$$", LEN(A1)
- LEN(SUBSTITUTE(A1, "\", "")))) + 1, 255) & "]Sheet1'!G26")


In article ,
"Cathy W" wrote:

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! :-)