Indirect - Why does it seem to only work sometimes?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

marston.gould@alaskaair.com - 20 Jun 2006 04:06 GMT

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?

Reply to this Message

Max - 20 Jun 2006 04:36 GMT

> 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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>
> Two situations:
[quoted text clipped - 18 lines]
>
> Why the difference?

Reply to this Message

Peo Sjoblom - 20 Jun 2006 04:36 GMT

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

Signature

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:
>
[quoted text clipped - 17 lines]
>
> Why the difference?

Reply to this Message

Peo Sjoblom - 20 Jun 2006 04:39 GMT

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

Signature

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
[quoted text clipped - 29 lines]
>>
>> Why the difference?

Reply to this Message

marston.gould@alaskaair.com - 20 Jun 2006 04:46 GMT

> Actually you don't have to remove the leading apostrophe in B42
>
[quoted text clipped - 55 lines]
> >>
> >> 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.

Reply to this Message

Arvi Laanemets - 20 Jun 2006 07:14 GMT

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
> >
[quoted text clipped - 66 lines]
> 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.

Reply to this Message

Peo Sjoblom - 21 Jun 2006 04:51 GMT

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

Signature

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
>>
[quoted text clipped - 67 lines]
> 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.

Reply to this Message

David McRitchie - 20 Jun 2006 04:43 GMT

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> Two situations:
>
[quoted text clipped - 17 lines]
>
> Why the difference?

Reply to this Message