path for Linked worksheets

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

I am creating spreadsheets with links to various other
workbooks. That is no problem but I really need to be
able to create in a cell(s) in the main work sheet which
contains the link path and workbook name to make use
easier in many ways.

Any help gratefully appreciated

Thanks
 
Hi Alan
you have to use the function INDIRECT. So for example enter the
following
=INDIRECT("'" & path_cell & "[" & workbook_cell & "]" & worksheet_cell
& "'!$A$1")
Will get the cell value form $A$1
BUT this will work only if the second workbook is open. INDIRECT will
not
work for closed files!.

If you have to reference closed files based on other cells, you may
have a look at the free add-in Morefunc.xll at
http://longre.free.fr/english/
This includes a function INDIRECT.EXT which will overcome Excels
restrictions regarding closed files. Just replace INDIRECT with
INDIRECT.EXT

HTH
Frank
 
you have to use the function INDIRECT. So for example enter the
following
=INDIRECT("'" & path_cell & "[" & workbook_cell & "]" & worksheet_cell
& "'!$A$1")
Will get the cell value form $A$1
BUT this will work only if the second workbook is open. INDIRECT will
not work for closed files!.
Click to expand...

And for open files, since Excel can't open multiple files with the same base
filename, the path component in the formula above is superfluous. Better not to
include it at all in INDIRECT calls. So simplify to

=INDIRECT("'["&workbook_cell&"]"&worksheet_cell&"'!$A$1")
If you have to reference closed files based on other cells, you may
have a look at the free add-in Morefunc.xll at
http://longre.free.fr/english/
This includes a function INDIRECT.EXT which will overcome Excels
restrictions regarding closed files. Just replace INDIRECT with
INDIRECT.EXT
Click to expand...

Pathname is useful in INDIRECT.EXT.
 
Thanks to you both but cannot get this to work. Do I
enter workbook_cell and worksheet_cell just as typed here
or what references do I give. Sorry must be a bit thick
today

Alan
-----Original Message-----
you have to use the function INDIRECT. So for example enter the
following
=INDIRECT("'" & path_cell & "[" & workbook_cell & "]" & worksheet_cell
& "'!$A$1")
Will get the cell value form $A$1
BUT this will work only if the second workbook is open. INDIRECT will
not work for closed files!.
Click to expand...

And for open files, since Excel can't open multiple files with the same base
filename, the path component in the formula above is superfluous. Better not to
include it at all in INDIRECT calls. So simplify to

=INDIRECT("'["&workbook_cell&"]"&worksheet_cell&"'!$A$1")
If you have to reference closed files based on other cells, you may
have a look at the free add-in Morefunc.xll at
http://longre.free.fr/english/
This includes a function INDIRECT.EXT which will overcome Excels
restrictions regarding closed files. Just replace INDIRECT with
INDIRECT.EXT
Click to expand...

Pathname is useful in INDIRECT.EXT.
Click to expand...
 
Hi Alan
you have to replace workbook_cell and work_sheet cell with your cell
reference in which you store the worksheet name and/or workbook name.
E.g.
A1: Book.xls
A2: Sheet1
Then use the INDIRECT formula as follows (As Harlan correctly pointed
out, I skipped the path_cell part):
=INDIRECT("'[" & A1 & "]" & A2 & "'!$A$1")

As posted before. Will work only if your workbook 'Book.xls' is opened

HTH
Frank
 
Back
Top