Data pulled from another spreadsheet not refreshing

[Jabe]

Hello,

I have a simple XLOOKUP formula that pulls data from one spreadsheet to another - there's no wrapping of any other formulas. Generally the formula works fine, but if the source sheet isn't open sometimes the data doesn't refresh, or I get a #N/A or #REF! error. As soon as the source sheet is opened the formula works again. Is there any way to fix this so the source sheet doesn't need to be opened?

Both documents are - and need to be - saved on SharePoint. Not sure if that makes any difference.

Thanks

 

[Joe4]

Both workbooks must be open in order for the XLOOKUP function to work. It cannot reference a closed workbook, or else you will get the #REF error.

Note that the VLOOKUP function will work on a closed file. I do not know if your data is structured in a way that allow you to use VLOOKUP.
If not, maybe you can alter your data layout so you can use VLOOKUP.

Vlookup not working when source files are closed

Hey guys, I have a file with a lot of linked data, which can't find any data via the Vlookup function when the source files are closed. It works perfectly until I decide I want to sort the data in a different way, which is when I get #REF! errors all over the place. However, when I open the...

www.mrexcel.com