dynamic external cell reference

[Guest]

Any help is appreciated... really stuck on this.
I am trying to reference an external cell where the file name
changes/corresponds to a particular local cell value.

For example:
Column B= Username.
Cell B2 = [JOE].

I want to get the data from cell N6 in the excel spreadsheet named
c:\data[joe].xls

I want to use a formula that uses the username in the cell reference... In
the above example, I'm trying something like
=data'B2'.xls!N6

to try and get
=dataJOE.xls!N6.

I'm also trying INDIRECT() function...
in C2 ="data"&B2&".xls!N6" which returns "datajoe.xls!N6"
then in D2 I use =Indirect(C2)
but can't get D2 to actually return the value for =dataJOE.xls!N6

Thanks
B
_________________
An idea is salvation by imagination.
- Frank Lloyd Wright

 

[Biff]

Hi!

A couple of things:

datajoe.xls!N6

You don't specify the sheet name.
The reference to C2 doesn't work because C2 is a formula
that returns the value datajoe.xls!N6.
Using Indirect requires that the other WB be opened.

Try this:

=INDIRECT("[data"&B2&".xls]sheet1!N6")

Replace sheet1 with the appropriate sheet name.

Biff

 

[Harlan Grove]

A couple of things:

datajoe.xls!N6

You don't specify the sheet name.

If these were all single worksheet Excel 2.x/3.x workbooks (or later
workbooks with a single worksheet with the same name as the base filename
without the .xls extension), then datajoe.xls!N6 is a valid external
reference.

 

[Myrna Larson]

Hi, Harlan:

Would you care to speculate on the probability that either of your conditions
is true in February, 2005?

Myrna

 

[Harlan Grove]

Would you care to speculate on the probability that either of your
conditions is true in February, 2005?

....

Depends on what application is generating the OP's .xls files. If Excel,
then maybe 4-to-1 against. If some creaky in-house character mode app (or
even Crystal Reports), then 4-to-1 in favor.

Just pointing out that the OP's formula *COULD* be syntactically valid. In
such cases, better to *ASK* the OP if that's what they really mean than just
to assume they screwed up. Believe it or not, barring inconsistencies I
assume OPs do know what they're talking about and have no patience with
respondents who second guess without seeking confirmation. You prefer to
believe OPs are ignoramuses?