I have 2 files A.xlsx and B.xlsm. File A is simply a data file that file B uses. File is A is quite large and I would rather have file B read A's select contents without opening A (plus I have quite of a bit of A's in my real application). Generally, excel does this pretty easily if I specify the entire path to the A file like this: ='C:\Desktop\[A.xlsx]Sheet1'!A1.
What I'd like to do is use the INDIRECT function so I can refer to different areas of A realtively easily, but when trying to use this function (here's a simple example =INDIRECT("'C:\Desktop\[A.xlsx]Sheet1'!A1") or =INDIRECT("'C:\Desktop\[A.xlsx]Sheet1'!A"&row_number), I get a #REF! error. Any idea how to do this so it works? Or is there another way to do what I am after without using INDIRECT? All I need to be able to do is keep the data file closed while being able to control what area of the data file B.xlsm reads, which is what I am able to do with INDIRECT.
Thank you.
Bookmarks