Filename in links

I have a sheet that will list certain results from many other sheets, I
would like the link to be dynamic so when I change the value in one cell it
calls a different sheet.

I use names as easier reference

example. Cell B6 contains
='001.xls'!Total
I would like to make the 001 to be created by a reference to cell B5 so if I
enter 002 into B5, B6 links to '002.xls'!Total
Something like ' + B5 & ".xls" + '!Total - but that does not work.

I have tried concatinating and many other minpulations but have not found
the answer yet

Hi Dave

you can use INDIRECT for this purpose. Put the following into B6:
=INDIRECT("'[" & B5 & ".xls]Sheet_name'!A1")

This won't work if the other workbook is closed. You can use the
following Add-In MOREFUC.XLL (see http://longre.free.fr/english/)
The function INDIRECT.EXT will overcome this Excel restriction

HTH
Frank

Dave Griffiths wrote:
I have a sheet that will list certain results from many other sheets,
I would like the link to be dynamic so when I change the value in one
cell it calls a different sheet.

I use names as easier reference

example. Cell B6 contains
='001.xls'!Total
I would like to make the 001 to be created by a reference to cell B5
so if I enter 002 into B5, B6 links to '002.xls'!Total
Something like ' + B5 & ".xls" + '!Total - but that does not work.

I have tried concatinating and many other minpulations but have not
found the answer yet

Thanks I will give it a try

"Frank Kabel" skrev i melding
...
Hi Dave

you can use INDIRECT for this purpose. Put the following into B6:
=INDIRECT("'[" & B5 & ".xls]Sheet_name'!A1")

This won't work if the other workbook is closed. You can use the
following Add-In MOREFUC.XLL (see http://longre.free.fr/english/)
The function INDIRECT.EXT will overcome this Excel restriction

HTH
Frank

Dave Griffiths wrote:
I have a sheet that will list certain results from many other sheets,
I would like the link to be dynamic so when I change the value in one
cell it calls a different sheet.

I use names as easier reference

example. Cell B6 contains
='001.xls'!Total
I would like to make the 001 to be created by a reference to cell B5
so if I enter 002 into B5, B6 links to '002.xls'!Total
Something like ' + B5 & ".xls" + '!Total - but that does not work.

I have tried concatinating and many other minpulations but have not
found the answer yet

Dave Griffiths wrote:
Thanks I will give it a try

"Frank Kabel" skrev i melding
...

Hi Dave

you can use INDIRECT for this purpose. Put the following into B6:
=INDIRECT("'[" & B5 & ".xls]Sheet_name'!A1")

This won't work if the other workbook is closed. You can use the
following Add-In MOREFUC.XLL (see http://longre.free.fr/english/)
The function INDIRECT.EXT will overcome this Excel restriction

HTH
Frank

Dave Griffiths wrote:

I have a sheet that will list certain results from many other sheets,
I would like the link to be dynamic so when I change the value in one
cell it calls a different sheet.

I use names as easier reference

example. Cell B6 contains
='001.xls'!Total
I would like to make the 001 to be created by a reference to cell B5
so if I enter 002 into B5, B6 links to '002.xls'!Total
Something like ' + B5 & ".xls" + '!Total - but that does not work.

I have tried concatinating and many other minpulations but have not
found the answer yet




OK got the add-in and loaded it, but for some reason it will not handle
closed files, I get the same response from INDIRECT.EXT as I do from
INDIRECT. #REF is the error shown in the cell. If I open the linked file
the value updates OK. I don't have a problem if I use a direct link to
the cell with the file open or closed. I'm using Excel 2002 if that helps.

Any Ideas please.
DaveG

Hi Dave

INDIRECT.EXT works fine. You can do the following to test your formula
1. Have you included the full path to your other workbook?
2. Assuming you have a formula like this =INDIRECT.EXT("'" & pathname &
"[" & workbook_name & "]Sheet1'!A1")
- copy the part inside the paranthese in another cell and add the
equatation sign '=' (e.g. ="'" & pathname & ......)
- copy this resulting string in another cell, add the '=' sign and hit
enter. If this also results in a #REF error, something in your string
concatenating is wrong

It would be helpful if you post your complete formula which results in
the #REf error (including the values in referenced cells)

HTH
Frank


Dave Griffiths wrote:
Dave Griffiths wrote:
Thanks I will give it a try

"Frank Kabel" skrev i melding
...

Hi Dave

you can use INDIRECT for this purpose. Put the following into B6:
=INDIRECT("'[" & B5 & ".xls]Sheet_name'!A1")

This won't work if the other workbook is closed. You can use the
following Add-In MOREFUC.XLL (see http://longre.free.fr/english/)
The function INDIRECT.EXT will overcome this Excel restriction

HTH
Frank

Dave Griffiths wrote:

I have a sheet that will list certain results from many other
sheets, I would like the link to be dynamic so when I change the
value in one cell it calls a different sheet.

I use names as easier reference

example. Cell B6 contains
='001.xls'!Total
I would like to make the 001 to be created by a reference to cell
B5 so if I enter 002 into B5, B6 links to '002.xls'!Total
Something like ' + B5 & ".xls" + '!Total - but that does not work.

I have tried concatinating and many other minpulations but have
not
found the answer yet




OK got the add-in and loaded it, but for some reason it will not
handle closed files, I get the same response from INDIRECT.EXT as I
do from INDIRECT. #REF is the error shown in the cell. If I open the
linked file the value updates OK. I don't have a problem if I use a
direct link to the cell with the file open or closed. I'm using Excel
2002 if that helps.

Any Ideas please.
DaveG

Frank Kabel wrote:
Hi Dave

INDIRECT.EXT works fine. You can do the following to test your formula
1. Have you included the full path to your other workbook?
2. Assuming you have a formula like this =INDIRECT.EXT("'" & pathname &
"[" & workbook_name & "]Sheet1'!A1")
- copy the part inside the paranthese in another cell and add the
equatation sign '=' (e.g. ="'" & pathname & ......)
- copy this resulting string in another cell, add the '=' sign and hit
enter. If this also results in a #REF error, something in your string
concatenating is wrong

It would be helpful if you post your complete formula which results in
the #REf error (including the values in referenced cells)

HTH
Frank


Dave Griffiths wrote:

Dave Griffiths wrote:

Thanks I will give it a try

"Frank Kabel" skrev i melding
. ..


Hi Dave

you can use INDIRECT for this purpose. Put the following into B6:
=INDIRECT("'[" & B5 & ".xls]Sheet_name'!A1")

This won't work if the other workbook is closed. You can use the
following Add-In MOREFUC.XLL (see http://longre.free.fr/english/)
The function INDIRECT.EXT will overcome this Excel restriction

HTH
Frank

Dave Griffiths wrote:


I have a sheet that will list certain results from many other
sheets, I would like the link to be dynamic so when I change the
value in one cell it calls a different sheet.

I use names as easier reference

example. Cell B6 contains
='001.xls'!Total
I would like to make the 001 to be created by a reference to cell
B5 so if I enter 002 into B5, B6 links to '002.xls'!Total
Something like ' + B5 & ".xls" + '!Total - but that does not work.

I have tried concatinating and many other minpulations but have

not

found the answer yet



OK got the add-in and loaded it, but for some reason it will not
handle closed files, I get the same response from INDIRECT.EXT as I
do from INDIRECT. #REF is the error shown in the cell. If I open the
linked file the value updates OK. I don't have a problem if I use a
direct link to the cell with the file open or closed. I'm using Excel
2002 if that helps.

Any Ideas please.
DaveG



Hi Frank

I Finally got there, the main problem was I had the second ' after the
filename and not the sheet name, I could not see it, took looking again
this morning before I found it.

Thanks again for your patience and help.

DaveG