Lookup to other workbook not working

[ammartino44]

Hello. I created a lookup to another workbook but it isn't working and I can't tell why. Spreadsheet attached with name of workbook and the formula that I'm using. Thanks.

[FDibbins]

INDIRECT() only works on open workbooks.

If having the other WB open is not an option, take a look at the More-func addin

[ammartino44]

The workbook is open. In the text box, I have the reference to the open formula and what that looks like and then in the cell above I have my formula that seems like it matches but it's throwing a #Ref error.

[FDibbins]

I can see nothing glaringly wrong with your formula. Try breaking it down a few steps into it's component parts.

=INDIRECT("'[Management Book "&E$3&".xlsx]Consolidation-F'!$A$7:$C$340") will return a error because (I think) you have a range where a songle cell is expected

So, try just...
=VLOOKUP($A5,INDIRECT("'[Management Book "&E$3&".xlsx]Consolidation-F'!$A$7:$C$340"),2,0)
See if that returns what you expect

Make sure that the text in E3 matches exactly what the file contains

If you still have a problem, upload a small version of the other file

[ammartino44]

This is the worksheet with all data cleared.I'll try your recommendations as well.

[ammartino44]

I didn't really understand your explanation about the single cell. Even if you put in the vlookup, the indirect is still looking at a range (it's just within the vlookup). Thanks for your help.

[ammartino44]

Figured it out. My range wasn't range correct for the match. Stupidest thing ever haha. Still interested in to why the indirect would return a value error unless it's used in vlookup. Thanks.

[FDibbins]

Still interested in to why the indirect would return a value error unless it's used in vlookup.

because, essentially, you are putting this into a cell =$A$7:$C$340

you are welcome, thanks for the rep