copy of formula contains file names into other cells
 

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

copy of formula contains file names into other cells
 

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

copy of formula contains file names into other cells
 

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




"Vergel Adriano" ...
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