Combining text in excel cells to create a formula

[Don McIvor]

I have a template and I want to make a whole heap of cells
refer to a another file. If someone enters the file name
in one cell, I want some other cells to look at that file.
I have tried using the formula:

=CONCATENATE("='[",A7,".xls]Sheet1!$B$2"),

where A7 is the filename, but it returns the required
formula, rather than calculating the formula. ie it
returns ='[Filename.xls]Sheet1'!$B$2, rather than 32. I
thought there might be a function that told excel to
calculate it as a formula but couldn't find one.

All help is much appreciated.

 

[TIM]

THIS IS SOMETHING LIKE WHAT I'M TRYING TO ACHIEVE.
(THE POST JUST BELOW YOURS)
DID YOU GET IT TO WORK?

-----Original Message-----
Don

Try using INDIRECT(). This will direct Excel to the address in the cell.

Andy.

I have a template and I want to make a whole heap of cells
refer to a another file. If someone enters the file name
in one cell, I want some other cells to look at that file.
I have tried using the formula:

=CONCATENATE("='[",A7,".xls]Sheet1!$B$2"),

where A7 is the filename, but it returns the required
formula, rather than calculating the formula. ie it
returns ='[Filename.xls]Sheet1'!$B$2, rather than 32. I
thought there might be a function that told excel to
calculate it as a formula but couldn't find one.

All help is much appreciated.

.