Excel formula referencing another Excel workbook on Sharepoint/Onedrive

Hi Guys.

I have searched far and wide and scoured every nook and cranny, phoned all support lines and just gotten nowhere...

I have the biggest favour to ask from someone who can assist.

Please note I am not an Excel power user, I don't know all that much about Excel, I am only a Systems Admin and my client require this after I recently moved them over to Sharepoint/Onedrive.

My client is using the =SUMIF formula to basically look for a certain "text" (I say text because the column is formatted as text so to insert 013, it displays as '013) in a column on a different workbook (both are in the same folder on the same Sharepoint address), uses that as a reference point to look up hours worked and then does the sum.

The exact formula is: =SUMIF('my sharepoint address/Documents/Documents/Excel test/[HOURS CLOCK MACHINES MASTER WEEK 10 2019.xlsx]Sheet1'!$A$4:$A$186;$A9;'my sharepoint address/Documents/Documents/Excel test/[HOURS CLOCK MACHINES MASTER WEEK 10 2019.xlsx]Sheet1'!$D$4:$D$186)

Problem is it comes back as #VALUE!.

What can I do to correct this?

Thanks in advance.

Hi Ali.

Thanks for the response. Please see attached files.

I dont know how to explain this.