Indirect function refer to other workbook

[Tang]

Dear all

I am looking for a function like "indirect" that able to to refer to a cell
in other workbook.

Eg Cell A1 in ABC.xls Sheet 1 is "Good", then the function should work like:
=indirect('[ABC.xls]Sheet1'A1) will give me the result "Good".

regards
Tang

 

[Frank Kabel]

Hi
why not use
='[ABC.xls]Sheet1'A1

 

[JulieD]

Hi Tang

not sure why you need to use an indirect function here .. won't
=[abc.xls]Sheet1!$A$1
do what you want

if however, A1 on in the second book was
[abc.xls]Sheet1!$A$1
then in that book
=indirect(A1)
will return Good.
this however, only appears to work when ABC.xls is open.

Maybe if you explain a bit more what you're trying to achieve then we can
assist further.

Regards
JulieD

 

[Tang]

Thanks JulieD.

The reason is that i have 2 set of 80 over files with standard format. I
need to compare the contents of certain cells. I am thinking of use a
standard setting for all by changing filename only.

Hi Tang

not sure why you need to use an indirect function here .. won't
=[abc.xls]Sheet1!$A$1
do what you want

if however, A1 on in the second book was
[abc.xls]Sheet1!$A$1
then in that book
=indirect(A1)
will return Good.
this however, only appears to work when ABC.xls is open.

Maybe if you explain a bit more what you're trying to achieve then we can
assist further.

Regards
JulieD

Dear all

I am looking for a function like "indirect" that able to to refer to a cell
in other workbook.

Eg Cell A1 in ABC.xls Sheet 1 is "Good", then the function should work like:
=indirect('[ABC.xls]Sheet1'A1) will give me the result "Good".

regards
Tang

 

[JulieD]

Hi Tang

you might like to have a look at these newsgroup articles
http://www.google.com/[email protected]
and
http://groups.google.com/groups?hl=...&[email protected]

Regards
JulieD

Thanks JulieD.

The reason is that i have 2 set of 80 over files with standard format. I
need to compare the contents of certain cells. I am thinking of use a
standard setting for all by changing filename only.

Hi Tang

not sure why you need to use an indirect function here .. won't
=[abc.xls]Sheet1!$A$1
do what you want

if however, A1 on in the second book was
[abc.xls]Sheet1!$A$1
then in that book
=indirect(A1)
will return Good.
this however, only appears to work when ABC.xls is open.

Maybe if you explain a bit more what you're trying to achieve then we can
assist further.

Regards
JulieD

Dear all

I am looking for a function like "indirect" that able to to refer to a cell
in other workbook.

Eg Cell A1 in ABC.xls Sheet 1 is "Good", then the function should work like:
=indirect('[ABC.xls]Sheet1'A1) will give me the result "Good".

regards
Tang

 

[kcowan]

Static reference to separate worksheet

I have a worksheet that contains historical data for a series of stocks. Each month I insert the latest data into column E and shift the history over to the right.

I have another worksheet that analyses price trends for previous quarters. But these shift over a column each time and I want to keep the references static.
I can do this with INDIRECT("Data!E1") but I need a way to automatically generate all the references INDIRECT("Data!E2") etc. for the 200 rows.

Or is there another way to accomplish this? Thanks.