Cell Reference Formula Outside Workbook

M

mikeismag

I am trying to reference a cell outside of my current spread sheet. I
am using a cell in my current sheet as an input for the extension of
the file I would like to look in. So I have this defined as LINK. And

the value of link is something like C:\\Myfolder\ready.xls

I am trying to set up a formula in my sheet that will use this
reference to the cell on the first page, which I defined as LINK so
when I change the value of the cell all my values will update to the
new folder and document, but retain their previous page, column and row

references.


For example. My current formula reads
[C::\\Myfolder\ready.xls]Sheet1!B6


I'm thinking I should be able to use LINK&Sheet1!B6


This should make it possible to continue to update the link reference
on my page.


Any help would be greatly appreciated.


Mike
 
D

Dave Peterson

You'd want to use the =indirect() function, but that only works when the
"sending" workbook is open.

But Harlan Grove wrote a UDF called PULL that will retrieve the value from a
closed workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

I am trying to reference a cell outside of my current spread sheet. I
am using a cell in my current sheet as an input for the extension of
the file I would like to look in. So I have this defined as LINK. And

the value of link is something like C:\\Myfolder\ready.xls

I am trying to set up a formula in my sheet that will use this
reference to the cell on the first page, which I defined as LINK so
when I change the value of the cell all my values will update to the
new folder and document, but retain their previous page, column and row

references.

For example. My current formula reads
[C::\\Myfolder\ready.xls]Sheet1!B6

I'm thinking I should be able to use LINK&Sheet1!B6

This should make it possible to continue to update the link reference
on my page.

Any help would be greatly appreciated.

Mike
Click to expand...
 
Top