Your first example was a reference in itself and went to find the value of
your reference. Your second example was not a reference but a string.

Example:

If you type...
=A1
into cell B1, it will return whatever cell A1 contains;

If instead you type...
="A1"
into B1, then B1 will see that it needs to display a string;

Using indirect turns a string into a reference, so typing...
=indirect("A1")
into B1 will returns whatever cell A1 contains.

But as I mentioned in my previous post, INDIRECT doesn't work with
references to other workbooks when the workbooks in question are not open.

"stew" wrote:

So how does the First path work

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road
Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111

and gives the result

So is this the only way to do this ,do you think.

Best

Stewa

"FiluDlidu" wrote:

"stew" wrote:
Hi all
What am I missing
='C:\Users\Welcome\Stewarts Files\Road accounting program\Road
Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111
Displays the contents of C111 IN WORK BOOK "Tour Managers
Spreadsheet.xls"tour managers spreadsheet

I'm guessing this is what you want for a result, is it not?

=("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)
&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")
Just displays the text of the formula and not the contents of the C111 CELL

Your formula was indeed designed to display a path and a cell reference at
the end. To have the result of Excel looking down this path and read the
value of the given reference, you would need to include your formula inside
the INDIRECT function, but unfortunately, INDIRECT doesn't work with
references to an external workbook that is not open, so you will get an error
for this formula every time "Tour Managers Spreadsheet.xls" is not open.

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")