Using Index and Match functions on a closed workbook

Hello

I am trying to run a function to gather data from a worksheet in a closed workbook. I am using the Index and Match functions. I have read that some functions can pull data from closed workbooks and some functions cannot. I would like some clarity on this. The 2 functions I am using are Index and Match and they return a REF error when the workbook is not open.

I am using Excel 2010 and here is the somewhat lengthy formula (mostly lengthy due to the file location reference). I am using Ctrl-Shift-Enter and the formula does work when the Parts - Mast List.xlsx file is open but not while it is closed.

{=INDEX('W:\Purchasing\Databases\Parts - Master List.xlsx'!Parts_Table[#Data],MATCH(1, (E42='W:\Purchasing\Databases\Parts - Master List.xlsx'!Parts_Table[Part
Number]) * (D42='W:\Purchasing\Databases\Parts - Master List.xlsx'!Parts_Table[Supplier]),0),MATCH("*Price",'W:\Purchasing\Databases\Parts - Master List.xlsx'!Parts_Table[#Headers],0))}

Thank you for your help.

Thank you very much. Using a specific range reference does work. Using a table reference is my preference because the table changes but I will try to work with that limitation. Does anyone understand why table references don't work when the sheet being referenced is closed?

Cheers!

What if you simply gave someone a folder with both files (the open and the closed)?

Would you still need to put the entire path name, or can you reference just that portion of the path that is one level up? i.e., the folder in which you are sitting?