Formula error: Excel cannot find file location (when it's moved)

[A819A1L]

Hi.  I've been having an issue with referencing other workbooks in a formula. When the workbook being referenced is moved, excel cannot find the file location even when I update the formula with the new file path or select the file to copy from.  I've searched for answers and I understand that this is a Microsoft problem? I use Excel 2016 on a Mac.

Are there any work-arounds/VBA codes to avoid having to re-type a spreadsheet full of formulas? If not, does anyone know if Microsoft is working on a solution? 

Cheers

[#2]

@A819A1L here is an example of one of the formulas:

SUMIFS('/Volumes/Share/Analysis/10. Raw Data/FY2019/[Master Commercial Data 2019.xlsx]Raw Data - Jobs'!$BD:$BD,'/Volumes/Share/Analysis/10. Raw Data/FY2019/[Master Commercial Data 2019.xlsx]Raw Data - Jobs'!$E:$E,$C$5,'/Volumes/Share/Analysis/10. Raw Data/FY2019/[Master Commercial Data 2019.xlsx]Raw Data - Jobs'!$F:$F,C$7)

The new file path is /User/adavies/DocCentral/Analysis/8. Reporting/BI-Annual/