copy of formula contains file names into other cells

[Turk]

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

 

[Vergel Adriano]

Try this:


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

 

[Turk]

Thank you Vergel,

I've tried but find my formula is much more complicated to fit in your
suggestion.

My forumla is :
=VLOOKUP($A2,'C:\temp\[GDP(2N).xls]GDP(2N)'!$A:$C,3)

where the 2nd GDP(2N) is the sheet name which changes with the file name.

Any more suggestion please?


Turk

Try this:


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

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