Referring a cell

[Terry0928 via OfficeKB.com]

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

 

[Luke M]

For number 1, I'm afriad you're probably out of luck. The closest thing
would be the INDIRECT function, but it won't work on closed workbooks. You
could use the HYPERLINK function, and concatenate a string together, but
that would simply open the file, not retrieve the specific data.

For number 2, did you try and type the dollar signs? Alternatively, after
clicking on the cell, hit the F4 key. This cycles through the different
options for relative/absolute referencing.

 

[Dave Peterson]

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/3000-2077_4-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.

 

[Terry0928 via OfficeKB.com]

Thanks Luke,

For number 1, does it mean I need to open the workbook and use indirect
function to link the cell from workbook 1 to workbook 2, which will indicate
like "='M:\Payroll\Expenses Report\[NON-
GATE REPORT APR 2010.working.xls]APR 10'!$G$38"?

For number 2, I did hit the F4 key on every cell, but if there any option
that I don't need to hit the F4 cell by cell in case of over 100 cells to
work on?

Regards,
Terry

For number 1, I'm afriad you're probably out of luck. The closest thing
would be the INDIRECT function, but it won't work on closed workbooks. You
could use the HYPERLINK function, and concatenate a string together, but
that would simply open the file, not retrieve the specific data.

For number 2, did you try and type the dollar signs? Alternatively, after
clicking on the cell, hit the F4 key. This cycles through the different
options for relative/absolute referencing.

Hi,

[quoted text clipped - 16 lines]

Many Thanks
Terry