I'm trying to include a cell reference as part of an ext. link
 

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.

I'm trying to include a cell reference as part of an ext. link
 

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.

I'm trying to include a cell reference as part of an ext. link
 

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.

I'm trying to include a cell reference as part of an ext. link
 

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.

I'm trying to include a cell reference as part of an ext. link
 

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