Try this:


=INDIRECT("'C:\temp\[" & $B$1 & ".xls]Sheet1'!$A$3") * 3

"Turk" wrote:

Dear all,

I have a worksheet of 2 columns, cells B1, C1 are the file names of 2 excel
files.
B2, C2 contain the same formula which retrieve data from those 2 files.


A B
C
GDP(2N)
ALP(1N)
Total ='C:\temp\[GDP(2N).xls]Sheet1'!$A$3*3
='C:\temp\[ALP(1N).xls]Sheet1'!$A$3*3

Since the formulae B2 & C2 are similiar but only the files name are
different,
how can I rewrite the formula in B2 so that I can just copy it into other
cells but not correct the file name all the time.

i.e. ='C:\temp\["B1".xls]Sheet1'!$A$3*3
='C:\temp\["C1".xls]Sheet1'!$A$3*3


Thanks


Turk