Reference another workbook using INDIRECT

[bab01824]

First, I've read similar posts and tried the answers but they're not working in my workbook.

Here is my formula;
=VLOOKUP(TRIM(CONSOLIDATION[@[Host Name]]),INDIRECT("'[INSERT TRUE UP WORKBOOK NAME HERE]"&CONSOLIDATION[@[SITE-FE]]&"'!$B$9:$J$8361"),7,FALSE))

Instead of substituting the referenced workbook "INSERT NEW WORKBOOK NAME HERE" on 600 formulas in the table each month I'd like to paste the referenced workbook name into a cell in this workbook and be done with it. The referenced workbook could be open or not, and the cell containing the workbook name could either be a named range (ie; TRUE_UP_REF) or not.

 

[texasalynn]

indirect will not work for closed workbooks.

See this
http://www.dailydoseofexcel.com/arc...s-to-retrieve-data-from-underlying-workbooks/

 

[BenMiller]

The referenced workbook could be open or not

False. INDIRECT works only if the referenced workbook is open.

One option is to download MoreFunc and use INDIRECT.EXE, or use this code.

 

[bab01824]

OK, so forget the requirement that the reference workbook is closed. And I don't want to include any VBA functions because of the way this workbook is distributed. What's wrong with my formula syntax? It works fine if each formula contains the name of the (open) referenced workbook, but throws an error if I place the name of the referenced workbook into a cell and reference that cell.

 

[BenMiller]

As long as your referenced worksheet is open, try =VLOOKUP(TRIM(CONSOLIDATION[@[Host Name]]),INDIRECT("'"&A1&CONSOLIDATION[@[SITE-FE]]&"'!$B$9:$J$8361"),7,FALSE))

Where A1 holds the variable.