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.