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

  • Thread starter Thread starter John M
  • Start date Start date
J

John M

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