I want to pull data from another workbook, but I want the name of the
workbook I pull from to be controlled from a drop-down in the main worksheet.
I have the drop-down part figured out, but I can't get that result
incorporated correctly into my link formula. The value in the drop down will
form part of the file name.

Use INDIRECT()..Try the below example .

In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation
marks).

In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks).
In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks).
In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks).

Save both workbooks.
In Book2, Sheet1, cell B1 type the following formula:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)

A1 = Workbook.xls
B1 = Sheet1
C1 = Hello
=INDIRECT("[" &A1 & "]" & B1 &"!" & "C1")

If this post helps click Yes
---------------
Jacob Skaria


"John M" wrote:

I want to pull data from another workbook, but I want the name of the
workbook I pull from to be controlled from a drop-down in the main worksheet.
I have the drop-down part figured out, but I can't get that result
incorporated correctly into my link formula. The value in the drop down will
form part of the file name.

still not quite working for me. any reason for it to be complicated by
having the source file in another directory? I had been messing around with
INDIRECT() already. It's returning the error #REF!. Just to ensure I'm
following your example below, the form should be:

=INDIRECT("initial part of file name" & cell I'm using in this file & "rest
of file name")

Correct?

"Jacob Skaria" wrote:

Use INDIRECT()..Try the below example .

In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation
marks).

In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks).
In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks).
In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks).

Save both workbooks.
In Book2, Sheet1, cell B1 type the following formula:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)

A1 = Workbook.xls
B1 = Sheet1
C1 = Hello
=INDIRECT("[" &A1 & "]" & B1 &"!" & "C1")

If this post helps click Yes
---------------
Jacob Skaria


"John M" wrote:

I want to pull data from another workbook, but I want the name of the
workbook I pull from to be controlled from a drop-down in the main worksheet.
I have the drop-down part figured out, but I can't get that result
incorporated correctly into my link formula. The value in the drop down will
form part of the file name.

OK, I've tracked down the source of the problem: if the file is open and I
follow the procedure below, it's fine. If I try to use the full path and
file name, no good.

"John M" wrote:

still not quite working for me. any reason for it to be complicated by
having the source file in another directory? I had been messing around with
INDIRECT() already. It's returning the error #REF!. Just to ensure I'm
following your example below, the form should be:

=INDIRECT("initial part of file name" & cell I'm using in this file & "rest
of file name")

Correct?

"Jacob Skaria" wrote:

Use INDIRECT()..Try the below example .

In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation
marks).

In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks).
In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks).
In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks).

Save both workbooks.
In Book2, Sheet1, cell B1 type the following formula:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)

A1 = Workbook.xls
B1 = Sheet1
C1 = Hello
=INDIRECT("[" &A1 & "]" & B1 &"!" & "C1")

If this post helps click Yes
---------------
Jacob Skaria


"John M" wrote:

I want to pull data from another workbook, but I want the name of the
workbook I pull from to be controlled from a drop-down in the main worksheet.
I have the drop-down part figured out, but I can't get that result
incorporated correctly into my link formula. The value in the drop down will
form part of the file name.

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

John M wrote:

I want to pull data from another workbook, but I want the name of the
workbook I pull from to be controlled from a drop-down in the main worksheet.
I have the drop-down part figured out, but I can't get that result
incorporated correctly into my link formula. The value in the drop down will
form part of the file name.

--

Dave Peterson