If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Filename in links
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 |
#3
|
|||
|
|||
Filename in links
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 |
#4
|
|||
|
|||
Filename in links
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 |
#5
|
|||
|
|||
Filename in links
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 |
#6
|
|||
|
|||
Filename in links
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 |
Thread Tools | |
Display Modes | |
|
|