External reference with variable workbook name?

KenV · 01-23-2006, 04:15 PM

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.

Barb Reinhardt · 01-23-2006, 05:00 PM

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.

KenV · 01-25-2006, 05:25 PM

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.

Barb Reinhardt · 01-30-2006, 09:35 AM

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.

KenV · 02-10-2006, 05:50 PM

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.

**pelican16** · 01-30-2019, 11:00 AM

Hello all, let me join please.
I have a similar issue but even after 3 hours I wasn't able to make it work. I would appreciate any advice.
Goal is to use vlookup to search in another workbook, while this is done daily and the workbooks' name changes
with every use (a new copy is created every day).

Workbook name should be like: Report_190130.xlsx where numbers change with date (Report_YYMMDD.xlsx)
This workbook is supposed to be closed when the formula runs.

I've already tried variations with function concat or text joining with '&' symbols and different apostrophe combinations,
but no success so far.

My best try was like:
=VLOOKUP(A1;A2;5;0)
A1: lookupvalue
A2: =CONCAT("'";B1;B2;".xlsx]";"'";"!";$A:$E)
B1: C:\Users\User\Desktop\[Report_
B2: 20190130

If I try to make the reference by direct click to the target workbook, it works.
(but it points to the exact workbook name - I need it to be variable)
Then I try to construct the same address with use of strings joined together - and nothing.
So the connection is for sure possible (no sharing problems or something like that)

**JeteMc** · 02-04-2019, 06:21 PM

Hello pelican16 and Welcome to Excel Forum.
Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Note that believe you will need to post your request in the VBA/ Macros forum since you say that you need the workbook to be closed while the closed while being searched.
Let us know if you have any questions.