Have you tried using editreplace? 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

"Alberto Ast" wrote in message
...
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(E71,"Duplicate","Yes"))

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


"Jacob Skaria" wrote:

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


"Alberto Ast" wrote:

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?