Replace part of file name in a formula based on sheet name

[Jenkins87]

I'm really hoping someone could help me out with this one. Here is my situation: I have a workbook with several sheets, all set up exactly the same. Each cell on a sheet is referencing the same file, however each sheet is referencing a different file.

For example, this is one of the cells in a sheet:

=IF(ISNA(VLOOKUP("iso1r2",'E:\BAM4\Tests\AnalysisChkA1\[B3LE1.xls]Dat'!$A$2:$CO$24,COLUMN(F2),FALSE)),"",VLOOKUP("iso1r2",'E:\BAM4\Tests\AnalysisChkA1\[B3LE1.xls]Dat'!$A$2:$CO$24,COLUMN(F2),FALSE))

And I would like the B3 to automatically be replaced with the sheet name in all cells.

Is there a function I can add into this formula, or would it be easy to create a VBA code (I currently do know how to write code)?

 

[JackDanIce]

Welcome to the board. Look at Excel's built in function INDIRECT: INDIRECT - Excel

 

[Jenkins87]

Thanks Jack! I've tried using the indirect function and can't seem to get it to work for this purpose. Any other ideas?