INDIRECT update with external workbook

[papa_face]

Hello,

Is it possible for the INDIRECT function to be tweaked so it updates even when the external workbook is closed?

Also does anyone have any idea why

=INDIRECT("C:\Documents and Settings\*****\Desktop\Excel[book2.xls]Sheet1!C"&C2)

Produces a #REF! error code?


Any help would be greatly appreciated :D

[VBA Noob]

See if links help

http://www.dailydoseofexcel.com/arch...sed-workbooks/
http://www.j-walk.com/ss/excel/tips/tip82.htm
http://www.contextures.com/xlFunctions05.html#RefWkbk

VBA Noob

[NBVC]

You could download and install a free Morefunc.dll addin from here: http://xcell05.free.fr/english/ and use the Indirect.ext function

=INDIRECT.EXT("C:\Documents and Settings\*****\Desktop\Excel[book2.xls]Sheet1!C"&C2)

[papa_face]

I've used the INDIRECT.EXT function and it works brilliantly. But ideally, I need to get it to work without installing add-in's into excel

Is there any other way to do this?

Also any feedback on the reason why my INDIRECT ^^^ function isn't returning a result with the filename?

Thanks!

[NBVC]

...

Also any feedback on the reason why my INDIRECT ^^^ function isn't returning a result with the filename?

Thanks!

should there be another \ after EXCEL?

[papa_face]

I tried:

=INDIRECT("C:\Documents and Settings\***\Desktop\Excel\[book2.xls]Sheet1!C"&C2)

It gave me:

#REF!

But

=INDIRECT("[book2.xls]Sheet1!C"&C2)

works.
I need it to use a full path to gain access to the file.

But having said that, INDIRECT is only meant to use open workbooks so it would never need the FULL path, just the sheet name.

Is there any other way of referencing a cell dynamically, like I have done in the above code, but so it will update automatically without the workbook open?

[NBVC]

How about:

=INDIRECT("'C:\Documents and Settings\***\Desktop\Excel\[book2.xls]Sheet1'!C"&C2)