reference to an external file

[Alberto Ast]

If I want to copy one cell from another file I will do

=[FileName.xls]SheetName!$I$10

But if I want the file to vary depending on a cell A1 value... I mean

If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls]
If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls]

How do I make a formula so the file name depend on the cell value
I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work....
How can I do it?

 

[Jacob Skaria]

USE INDIRECT()

=INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10")


'Try the below to get more familiar

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

 

[Alberto Ast]

It looks like it should work but my application is a little more complex
because I have SUMPRODUCT involved.... my actual formula on my cell is as
below

=IF(SUMPRODUCT(('[FM Serial Label Print Log -
R1.3.xls]Log'!$J$6:$J$15=F7)*('[FM Serial Label Print Log -
R1.3.xls]Log'!$K$6:$K$15<=G7)*('[FM Serial Label Print Log -
R1.3.xls]Log'!$L$6:$L$15>=G7))>0=FALSE,"Invalid
S/N",IF(E7>1,"Duplicate","Yes"))

Then I need the "FM" on the file name to depent in a cell value.
Can you help me out?

USE INDIRECT()

=INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10")


'Try the below to get more familiar

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

If I want to copy one cell from another file I will do

=[FileName.xls]SheetName!$I$10

But if I want the file to vary depending on a cell A1 value... I mean

If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls]
If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls]

How do I make a formula so the file name depend on the cell value
I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work....
How can I do it?

 

[Jacob Skaria]

Check out the below link where you can download an add-in called Morefunc
which has a function called INDIRECT.EXT

http://xcell05.free.fr/morefunc/english/index.htm

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

It looks like it should work but my application is a little more complex
because I have SUMPRODUCT involved.... my actual formula on my cell is as
below

=IF(SUMPRODUCT(('[FM Serial Label Print Log -
R1.3.xls]Log'!$J$6:$J$15=F7)*('[FM Serial Label Print Log -
R1.3.xls]Log'!$K$6:$K$15<=G7)*('[FM Serial Label Print Log -
R1.3.xls]Log'!$L$6:$L$15>=G7))>0=FALSE,"Invalid
S/N",IF(E7>1,"Duplicate","Yes"))

Then I need the "FM" on the file name to depent in a cell value.
Can you help me out?

USE INDIRECT()

=INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10")


'Try the below to get more familiar

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

If I want to copy one cell from another file I will do

=[FileName.xls]SheetName!$I$10

But if I want the file to vary depending on a cell A1 value... I mean

If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls]
If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls]

How do I make a formula so the file name depend on the cell value
I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work....
How can I do it?

 

[Don Guillett]

Have you tried using edit>replace? You could have a macro do this that could
even be tied to a worksheet_change event that automatically fired when
changing your cell a1 from FM to ??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

It looks like it should work but my application is a little more complex
because I have SUMPRODUCT involved.... my actual formula on my cell is as
below

=IF(SUMPRODUCT(('[FM Serial Label Print Log -
R1.3.xls]Log'!$J$6:$J$15=F7)*('[FM Serial Label Print Log -
R1.3.xls]Log'!$K$6:$K$15<=G7)*('[FM Serial Label Print Log -
R1.3.xls]Log'!$L$6:$L$15>=G7))>0=FALSE,"Invalid
S/N",IF(E7>1,"Duplicate","Yes"))

Then I need the "FM" on the file name to depent in a cell value.
Can you help me out?

USE INDIRECT()

=INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10")


'Try the below to get more familiar

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

If I want to copy one cell from another file I will do

=[FileName.xls]SheetName!$I$10

But if I want the file to vary depending on a cell A1 value... I mean

If cell A1 value is equal to Q1 then file name will be [Q1
FileName.xls]
If cell A1 value is equal to Q2 then file name will be [Q2
FileName.xls]

How do I make a formula so the file name depend on the cell value
I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work....
How can I do it?

 

[Dave Peterson]

First, if you're going to use =indirect(), then the sending file must be open.
You'll see an error if that sending workbook is not open.

=IF(SUMPRODUCT((indirect('["
& x9999 & " Serial Label Print Log - R1.3.xls]Log'!J6:J15")=F7)
* .....

If your file will be closed, 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.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.

It looks like it should work but my application is a little more complex
because I have SUMPRODUCT involved.... my actual formula on my cell is as
below

=IF(SUMPRODUCT(('[FM Serial Label Print Log -
R1.3.xls]Log'!$J$6:$J$15=F7)*('[FM Serial Label Print Log -
R1.3.xls]Log'!$K$6:$K$15<=G7)*('[FM Serial Label Print Log -
R1.3.xls]Log'!$L$6:$L$15>=G7))>0=FALSE,"Invalid
S/N",IF(E7>1,"Duplicate","Yes"))

Then I need the "FM" on the file name to depent in a cell value.
Can you help me out?

USE INDIRECT()

=INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10")


'Try the below to get more familiar

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

If I want to copy one cell from another file I will do

=[FileName.xls]SheetName!$I$10

But if I want the file to vary depending on a cell A1 value... I mean

If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls]
If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls]

How do I make a formula so the file name depend on the cell value
I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work....
How can I do it?

 

[Alberto Ast]

Thanks.. it has very good infomation.. for now I will stay with your previous
tips.

Check out the below link where you can download an add-in called Morefunc
which has a function called INDIRECT.EXT

http://xcell05.free.fr/morefunc/english/index.htm

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

It looks like it should work but my application is a little more complex
because I have SUMPRODUCT involved.... my actual formula on my cell is as
below

=IF(SUMPRODUCT(('[FM Serial Label Print Log -
R1.3.xls]Log'!$J$6:$J$15=F7)*('[FM Serial Label Print Log -
R1.3.xls]Log'!$K$6:$K$15<=G7)*('[FM Serial Label Print Log -
R1.3.xls]Log'!$L$6:$L$15>=G7))>0=FALSE,"Invalid
S/N",IF(E7>1,"Duplicate","Yes"))

Then I need the "FM" on the file name to depent in a cell value.
Can you help me out?

USE INDIRECT()

=INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10")


'Try the below to get more familiar

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


:

If I want to copy one cell from another file I will do

=[FileName.xls]SheetName!$I$10

But if I want the file to vary depending on a cell A1 value... I mean

If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls]
If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls]

How do I make a formula so the file name depend on the cell value
I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work....
How can I do it?