We use Excel to maintain a 'dashboard' that reads/applies formulas from other workbooks on Sharepoint.
All files are on sharepoint (dashboard and source files), and never saved locally. This is to make sure that anyone who opens the dashboard will be able to use it.

Tonight after putting in the formulas for the path to this months data, everything worked until I closed the source file. It then changed the correct values to #Value. When looking at the formula, as I originally put it in, it populated the formula like the file was local and it worked (source file was open). Example:
=Countif('source_data_april.xlsx'!B:B,"High")

When I closed the source file, it changed the formula automatically to reflect the path to Sharepoint (abbreviating since the forum things I'm trying to post a link) =Countif('sharepoint2016.... source_data_april....) and the output broke, changing to #value.

Opening the source file, the formula reverts, and the values correct themselves.

MicroSoft was of no help, one person suggested doing find/replace to change all of the = to #, saving the file(s), closing the source, and then restoring the # to =, this didn't fix it either.

Is there a trick I'm missing to fixing this? We did not have this issue before our migration. I do not have any admin rights to sharepoint, so any solution needs to be done in excel.

Right now I'm building out a "formula workbook", getting ready for the worst case scenario of telling the people that update the dashboard that they will need to edit the month in the workbook for the source files, then cut-and-paste the formulas into the dashboard each month. I'm hoping there's a better solution. Right now we do the "hold and drag" to pull the column of formulas over, then highlight the new row and do find/replace for the month in just that row, and it's been great until this month.

Thank you all!