proper way to substitute a file name in a formula?

[bab01824]

I have this formula in WORKBOOK A that performs a VLookup in WORKBOOK B;

=IFERROR(IF(ISBLANK(CONSOLIDATION[@[Host Name]]),"",VLOOKUP(TRIM(CONSOLIDATION[@[Host Name]]),INDIRECT("'[WORKBOOK_B_Rev 1.1.0.xlsx]"&CONSOLIDATION[@[SITE-FE]]&"'!$B$9:$J$8361"),7,FALSE)),"Not Found")

This works well. But the file name of the reference workbook changes every time I run this formula, and the formula appears as many as 800 times in the workbook. I've been trying to enter the WORKBOOK B name in a cell in WORKBOOK A and have this formula reference it, but I can't seem to nail down the exact syntax. Can someone give me an example of what it would look like if, for example, the name of WORKBOOK B appears in cell A1 of WORKBOOK A?

 

[Jeff Meyers]

You want to use the INDIRECT function

The syntax would be =INDIRECT("'['&A1&".xlsx]worksheetname'!cellref")

Link to an example: http://support.microsoft.com/kb/213933

UPDATE:
I just looked at your formula more closely, and you are already using the INDIRECT function. You can remove the Workbook / File name from the formula and link to it in cell A1. You just need to concatenate before and after the A1 reference as in the syntax above.

 

[tmischler]

does this only work if both files are open?

 

[T. Valko]

does this only work if both files are open?

Yes, if using the INDIRECT function the source file must be open.