External reference with variable workbook name?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 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.

Barb Reinhardt said:
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

KenV said:
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.
Click to expand...
Click to expand...
 
Oops, I forgot something

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

KenV said:
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.

Barb Reinhardt said:
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

KenV said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
 
Back
Top