792 votesj commented
I fixed mine. I had issues with sumif( ) type functions looking in other files.
It takes a while but you only have to do this once.
Make sure all the connected files are in OneDrive.
Use the GetData wizard to import the data you are linking to for your calculations into the same spreadsheet as the formula. default behaviour is to dump the data into a new sheet as a Table. Rename the Table and the worksheet. add some text explaining where this random data comes from. because good practice.
Go to the formula that used to reference the data directly in the other worksheet (which now doesn't work) and point it to the new Table.
So long as the other workbooks aren't open, to get fresh data, just hit Data/RefreshAll.