Filter function in Excel 365 does not work with linked file which is open.

[bhavikbhagat]

I am facing problem when I refer to one excel file from other file thru Filter() in Excel 365.  

I have one large file (File A) with my data having more than 200000 records.  Just to see only specific information I want, I use another excel file (File B) which works as Dashboard for me.   I have kept both the files on my OneDrive folders so that I can access this from my office computer and from my laptop also when I am travelling.

I use Filter() in File B to fetch only relevant records from File A. The filter() in File B works perfectly fine when the File A is also open.  But when I close File A, the data file, Filter() in File A shows #REF! error.

When I see Edit Linked from File B, I can see that File A is a remote file the status is OK.  

When I open the data file FIle A again, all the formula in File B works perfectly fine again.  

I dont want to keep the data file open all the times for multiple reasons.

Can someone help me how do I handle this problem? 

[#2]

@bhavikbhagat 

Perhaps Power Query could be an option. The only it requires manual refresh to have recent changes in source file.

[#3]

Thanks for your reply Sergei. I thought that as well.
But With power query, when the other file A is open to update more data, power query does not get refreshed. it gives error that can not refresh query because the source file is in use. Can I set power query to run with open file as well. Although such situation comes less often, a solution always helps.

[#4]

@bhavikbhagat 

Yes, that's an issue if you query the file from mapped local drive. If get file from the cloud (File->Info-> Get path, remove ?web=1 at the end and use From Web connector) it works if source file is opened. But that requires internet connection which is not always suitable while travelling.

Not sure if the workaround for all cases exists.

[#5]

Hello @Sergei Baklan 

Thank you.  I will check this option.