The function you'd want to use is =indirect().
But =indirect() won't work if the sending file is closed.
Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm
That includes =indirect.ext() that may help you.
===
If you have trouble getting to the site, then search google for indirect.ext.
I found this alternative site:
http://download.cnet.com/Morefunc/30...-10423159.html
I didn't look to see if it was the most current version.
I'd check the original site every so often to see if it's working.
On 05/18/2010 11:23, Terry0928 via OfficeKB.com wrote:
Hi,
1) I would like to know how can I get "='M:\Payroll\Expenses Report\[NON-
GATE REPORT APR 2010.working.xls]APR 10'!$G$38 on a worksheet when referring
the date on G38 of the file "NON-GATE REPORT APR 2010.working.xls". I can
only get something like "='[Movement.0410.xls]Reconciliation HKAHQ'!$E$60"
and need to open the data file for referring to the cell data to paste on the
destination worksheet.
2) On sheet 2, if I want to type "= and point to a cell on sheet 1, the link
will be like "=Sheet1!A1" can I make the cell to read "=Sheet1!$A$1" ?
Please advise.
Many Thanks
Terry