Showing contents of cells in other xls file

[PBK]

Hi,

I want to show the contents of a specific cell from another xls file in my
worksheet.
This works fine using: ='C:\[TEST.xls]Blad1'!$A$1
On this way the contents of cell A1 are shown in my worksheet.

Since I have lots of cells I want to refer to I would like to have 1 cell in
a specific worksheet where the xls file is defined to get the data from. On
this way I would only have to define this file once in stead on a thousand
times.

Is this possible ??

Regards,

Pieter

 

[BrianB]

If in cell A1 you have :-
c:\temp\[test.xls]
then you can use something like :-
=INDIRECT("'" &$A$1 &"Sheet1'!$A$1")


Regards
BrianB
=============================================================

 

[PBK]

Hi Brian,

Thanks for your answer. It works, but there seems to be a difference. When I
use the INDIRECT function the other worksheet has to be open. that is not
the case using the direct method.

Any ideas ?

Regards,

Pieter

If in cell A1 you have :-
c:\temp\[test.xls]
then you can use something like :-
=INDIRECT("'" &$A$1 &"Sheet1'!$A$1")


Regards
BrianB
=============================================================




"PBK" <[email protected]> wrote in message

Hi,

I want to show the contents of a specific cell from another xls file in my
worksheet.
This works fine using: ='C:\[TEST.xls]Blad1'!$A$1
On this way the contents of cell A1 are shown in my worksheet.

Since I have lots of cells I want to refer to I would like to have 1 cell in
a specific worksheet where the xls file is defined to get the data from. On
this way I would only have to define this file once in stead on a thousand
times.

Is this possible ??

Regards,

Pieter

 

[Harlan Grove]

. . . When I
use the INDIRECT function the other worksheet has to be open. that is not
the case using the direct method.

....

That is an unavoidable limitation in Excel. Direct references can work with
both open and closed workbooks but without flexibility. Indirect references
provide flexibility but can only work with open workbooks. The reason for
the latter is because indirect references require using the INDIRECT
function (if you limit yourself to built-in functionality), INDIRECT only
returns range objects, and range objects can only come open workbooks.
Underlying this is the fact that direct external references resolve to range
objects when the workbook to which they refer is open, but to arrays when
the workbook is closed.

If you want flexibility in external references, you must go beyond built-in
functionality. See the following for alternatives.

http://groups.google.com/[email protected]

 

[PBK]

Thanks for the answer Harlan,

I will give it a try.

Regards,

Pieter

 

[ch]

I want to show the contents of a specific cell from another xls
file in my worksheet.
This works fine using: ='C:\[TEST.xls]Blad1'!$A$1
On this way the contents of cell A1 are shown in my worksheet.

Since I have lots of cells I want to refer to I would like to have
1 cell in a specific worksheet where the xls file is defined to
get the data from. On this way I would only have to define this
file once in stead on a thousand times.

Is this possible ??

Doesnt do what you want but maybe it get's you where you are going?



select all then replace 'c:\[test.xls]Blad1'
with 'c:\[test2.xls]Sheet1'

or any specific part thereof as long as its enough to make a
distinction (ie t.xls replace with t2.xls)