External reference with variable workbook name?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KenV - 23 Jan 2006 20:10 GMT

Hello everyone,

I'm referencing an external workbook and need part of the workbook name to
be a variable. My base formula is; [55421GNTForms.xls]SBR'!$C$5

I need "55421" to be a variable, with the new information pulled from a cell
within the referencing workbook. (55421 is a name, not a number.) There
will be a .xls file of the correct name already saved in the correct
referenced location.

What is the syntax for this reference? I've entered: ["A1"&GNTForms.xls]
SBR!$C$5 and numerous other combinations and have not had any luck.

I'm in the 'New Users' Group for a reason, so please dumb you answer down as
much as possible. I would greatly appreciate it.

Thanks.

Reply to this Message

Barb Reinhardt - 23 Jan 2006 20:58 GMT

First, this will only work if the workbook is OPEN. If the workbook is
closed, you'll need to have INDIRECT.EXT within MOREFUNC.xll installed.

http://xcell05.free.fr/

Let's say the file is in the following directory in your system:

A1: C:\KenV Documents\folder1 (location of the file you want)
B1: 55421
C1: = "'"&A1&"\["&B1"]55421GNTForms.xls]SBR'!$C$5"
D1: =indirect.ext(C1)

I hope I dumbed it down enough. It took me a while to figure out myself.
Come back if you can't get it working.

Barb Reinhardt

> Hello everyone,
>
[quoted text clipped - 13 lines]
>
> Thanks.

Reply to this Message

KenV - 25 Jan 2006 21:20 GMT

Hello Barb,

I can't seem to get it to work. It's giving me the "Formula contains an
error" message and it's highlighting B1"]GNTForms.xls]SBR'

Am I correct in assuming I need to take the '55421' out of the C1 formula?
That's what the ["&B1"] is for, right? That's the variable and I don't want
it in there twice.

I have the external workbook open and the Morefunc turned off.

Thanks for the help.

> First, this will only work if the workbook is OPEN. If the workbook is
> closed, you'll need to have INDIRECT.EXT within MOREFUNC.xll installed.
[quoted text clipped - 30 lines]
> >
> > Thanks.

Reply to this Message

Barb Reinhardt - 30 Jan 2006 13:29 GMT

Oops, I forgot something

C1: = "'"&A1&"\["&B1"]55421GNTForms.xls]SBR'!$C$5"
to
C1: = "'"&A1&"\["&B1&"]55421GNTForms.xls]SBR'!$C$5"

> Hello Barb,
>
[quoted text clipped - 43 lines]
> > >
> > > Thanks.

Reply to this Message

KenV - 10 Feb 2006 21:48 GMT

This is what finally worked. A co-worker of mine figured it out.

=CONCATENATE("'C:\Mitek62\jobs\",B10,"\[",B10,"GNTForms.XLS]","SBR'","!$AS$7")

I haven't addressed the INDIRECT issue yet.

Thank you for your consideration.

Reply to this Message