Indirect - Why does it seem to only work sometimes?

[marston.gould]

Two situations:

1) Create two new workbooks
2) In one workbook (Workbook1) enter value of 200 in cell A1
3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell
A1
4) In cell A2 in second workbook, enter =Indirect(A1)
5) I get back 200

Situation 2

1) I have two large workbooks
2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in
cell B304, I have value of
2,000,000
3) In another workbook, in cell B42, I have the following: '[Award
Stats]2006 Stats'!B304
4) In cell B43 in second workbook, I have =Indirect(B42)
5) I get back #REF!

Why the difference?

 

[Max]

4) In cell B43 in second workbook, I have =Indirect(B42)

Try instead in B43: =INDIRECT("'"&B42)

Looks like the preceding single quote in the text entered in B42 was
"swallowed" by Excel, re your line:

3) In another workbook, in cell B42, I have the following: '[Award
Stats]2006 Stats'!B304

Two situations:

1) Create two new workbooks
2) In one workbook (Workbook1) enter value of 200 in cell A1
3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell
A1
4) In cell A2 in second workbook, enter =Indirect(A1)
5) I get back 200

Situation 2

1) I have two large workbooks
2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in
cell B304, I have value of
2,000,000
3) In another workbook, in cell B42, I have the following: '[Award
Stats]2006 Stats'!B304
4) In cell B43 in second workbook, I have =Indirect(B42)
5) I get back #REF!

Why the difference?

 

[Peo Sjoblom]

It gets hung up on the apostrophe since it is both part of workbook/sheet
naming and also a text precedent, remove the leading apostrophe in B42
like this

[Award Stats.xls]Stats'!B304

then in B43 use

=INDIRECT("'"&B42)

also note that if you close the source workbook indirect will not work


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Peo Sjoblom]

Actually you don't have to remove the leading apostrophe in B42

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

It gets hung up on the apostrophe since it is both part of workbook/sheet
naming and also a text precedent, remove the leading apostrophe in B42
like this

[Award Stats.xls]Stats'!B304

then in B43 use

=INDIRECT("'"&B42)

also note that if you close the source workbook indirect will not work


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

Two situations:

1) Create two new workbooks
2) In one workbook (Workbook1) enter value of 200 in cell A1
3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell
A1
4) In cell A2 in second workbook, enter =Indirect(A1)
5) I get back 200

Situation 2

1) I have two large workbooks
2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in
cell B304, I have value of
2,000,000
3) In another workbook, in cell B42, I have the following: '[Award
Stats]2006 Stats'!B304
4) In cell B43 in second workbook, I have =Indirect(B42)
5) I get back #REF!

Why the difference?

 

[David McRitchie]

Without actually testing, perhaps what you see is different that what
you enter. The initial single quote would indicate a text entry and
would not be seen in the cell. Try with two single quotes at the beginning.

Actually I did just test this and it bears out my reply.
the workbook is 2004-11.xls so entire test can be done with same workbook
D8: ''[2004-11.xls]sheet146'!f11
D9: =INDIRECT(D8)
F11: 'F11text

 

[marston.gould]

Actually you don't have to remove the leading apostrophe in B42

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

It gets hung up on the apostrophe since it is both part of workbook/sheet
naming and also a text precedent, remove the leading apostrophe in B42
like this

[Award Stats.xls]Stats'!B304

then in B43 use

=INDIRECT("'"&B42)

also note that if you close the source workbook indirect will not work


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

Two situations:

1) Create two new workbooks
2) In one workbook (Workbook1) enter value of 200 in cell A1
3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell
A1
4) In cell A2 in second workbook, enter =Indirect(A1)
5) I get back 200

Situation 2

1) I have two large workbooks
2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in
cell B304, I have value of
2,000,000
3) In another workbook, in cell B42, I have the following: '[Award
Stats]2006 Stats'!B304
4) In cell B43 in second workbook, I have =Indirect(B42)
5) I get back #REF!

Why the difference?

Thanks - is there no way to do this w/o having the other worksheet
open?
The idea here is for a user to type in (or select from a validated
list) the name of a file.
The file will always be of a similar format (2006 Stats, 2005 Stats,
etc.). In some cases
it would be monthly rather than annually - not that that matters.

I'd want to limit the user's work to just picking the file name w/o
having to open all the other files as that sort of defeats the purpose.

 

[Arvi Laanemets]

Hi

I myself sometimes use some standard name for cases, where data are imported
from various source files. I.e. The source file is renamed to standard one,
data are imported from it to another workbook, then another source file is
renamed, etc.

The another way is to write a procedure, which asks for source file (File
Open Dialog), and then either rewrites all links ( using ReplaceAll) on
sheet(s), or imports data from selected source file into workbook.

There was some link to download an UDF working like INDERECT, but with
closed source file too, in some of Excel NG's some time ago.


Arvi Laanemets

Actually you don't have to remove the leading apostrophe in B42

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

It gets hung up on the apostrophe since it is both part of workbook/sheet
naming and also a text precedent, remove the leading apostrophe in B42
like this

[Award Stats.xls]Stats'!B304

then in B43 use

=INDIRECT("'"&B42)

also note that if you close the source workbook indirect will not work


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey



Two situations:

1) Create two new workbooks
2) In one workbook (Workbook1) enter value of 200 in cell A1
3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell
A1
4) In cell A2 in second workbook, enter =Indirect(A1)
5) I get back 200

Situation 2

1) I have two large workbooks
2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in
cell B304, I have value of
2,000,000
3) In another workbook, in cell B42, I have the following: '[Award
Stats]2006 Stats'!B304
4) In cell B43 in second workbook, I have =Indirect(B42)
5) I get back #REF!

Why the difference?

Thanks - is there no way to do this w/o having the other worksheet
open?
The idea here is for a user to type in (or select from a validated
list) the name of a file.
The file will always be of a similar format (2006 Stats, 2005 Stats,
etc.). In some cases
it would be monthly rather than annually - not that that matters.

I'd want to limit the user's work to just picking the file name w/o
having to open all the other files as that sort of defeats the purpose.

 

[Peo Sjoblom]

You would need an add-in, the easiest would probably be to download and
install Laurent Longre's Morefunc

http://xcell05.free.fr/english/


INDIRECT.EXT will work with closed workbooks
--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

Actually you don't have to remove the leading apostrophe in B42

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

It gets hung up on the apostrophe since it is both part of
workbook/sheet
naming and also a text precedent, remove the leading apostrophe in B42
like this

[Award Stats.xls]Stats'!B304

then in B43 use

=INDIRECT("'"&B42)

also note that if you close the source workbook indirect will not work


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey



Two situations:

1) Create two new workbooks
2) In one workbook (Workbook1) enter value of 200 in cell A1
3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell
A1
4) In cell A2 in second workbook, enter =Indirect(A1)
5) I get back 200

Situation 2

1) I have two large workbooks
2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in
cell B304, I have value of
2,000,000
3) In another workbook, in cell B42, I have the following: '[Award
Stats]2006 Stats'!B304
4) In cell B43 in second workbook, I have =Indirect(B42)
5) I get back #REF!

Why the difference?

Thanks - is there no way to do this w/o having the other worksheet
open?
The idea here is for a user to type in (or select from a validated
list) the name of a file.
The file will always be of a similar format (2006 Stats, 2005 Stats,
etc.). In some cases
it would be monthly rather than annually - not that that matters.

I'd want to limit the user's work to just picking the file name w/o
having to open all the other files as that sort of defeats the purpose.