Explicit links to workbooks on Sharepoint breaks since migration to OneDrive

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!

Thank you for the insight. Such a shame that MS had to add apparent zero-value complexity to this.

We have been pushing for other reporting methods around this (such as PowerBI), though all of those have their own obstacles.

If anyone else has seen this issue and found a way around it, please let me know.

Until then, I've got two approaches that can work in tandem. One is the list of formulas, which makes a good backup. The other is the
formula laden book, and when all is populated, copy and do a special-past as text only, then save the file with a unique filename
indicating it's the results, and not the formulas. That's the one well have everyone open. Still not ideal, but repeatable.