External reference with variable workbook name?
 

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.

External reference with variable workbook name?
 

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" wrote:

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.

External reference with variable workbook name?
 

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" wrote:


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" wrote:

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.

External reference with variable workbook name?
 

Oops, I forgot something

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

"KenV" wrote:

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" wrote:


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" wrote:

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.

External reference with variable workbook name?
 

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.