"VALUE" Error Issue

Question

Hi people,

I would like to ask a question regarding the use of Microsoft Excel in Office 365.

Being the middle-man for this issue, a team from the Marketing Department encounters an issue where the value/number is not updated normally like it should be, instead it shows "VALUE" just like in the attachment.

At first, I open all the individual Excel files one-by-one and finally I open the main Excel file. I get 2 errors right after opening the last file (see attachment):

Strangely, only 1 person in the team can edit the Excel file normally meaning it shows an actual number or value after calculation. And that person can also save the file without any issue.

Any help is utterly appreciated.

Thanks in advance!

<PII has been removed by forum moderator>

Answer 1

Site Collection administrators are given full control over all Web sites in the site collection. The author of the file is not necessarily a site collect administrator, you can upload the file to the site even if you are a member of that site when you have permission. You can contact SharePoint Online administrator as well, they can check the site collection administrator in SharePoint admin center.

Best Regards,

Zixuan

Hi Sir,

Alright understood. I will ask the Online Admin (my superior - IT person) regarding on that and the permission that the Collection Admin set for the Excel workbooks. Will update you soon.

Thanks Zixuan!

Answer 2

Hi Syazwan,

Site Collection administrators are given full control over all Web sites in the site collection. The author of the file is not necessarily a site collect administrator, you can upload the file to the site even if you are a member of that site when you have permission. You can contact SharePoint Online administrator as well, they can check the site collection administrator in SharePoint admin center.

Best Regards,

Zixuan

Answer 3

Based on my test, if I create two workbooks locally, set reference to one of the workbook, then upload that workbook to SharePoint, the reference in the formula won't change. Considering there are many workbooks in your case, you may contact the author of those files as well. In fact, if there isn't a reference refer to SharePoint site and the author confirmed that those files can be opened normally locally, the simplest method is to let the author upload those files to a folder in SharePoint again. Then you can download the files in that folder to see the outcome.   

Noted, Sir.

Besides, the site admin I mentioned refers to Site collection administrator (A site collection can have several admins). You may suggest the team leader to confirm whether he can manage that site collection and check the permission.

Regards,

Zixuan

Just to confirm, Site Collection Administrator means the owner of the documents/files, correct? Not the SharePoint Admin (which is usually the IT person), is it?

Thanks for your fast reply!

Answer 4

Hi Syazwan,

Thanks for the information you provided.

Based on my test, if I create two workbooks locally, set reference to one of the workbook, then upload that workbook to SharePoint, the reference in the formula won't change. Considering there are many workbooks in your case, you may contact the author of those files as well. In fact, if there isn't a reference refer to SharePoint site and the author confirmed that those files can be opened normally locally, the simplest method is to let the author upload those files to a folder in SharePoint again. Then you can download the files in that folder to see the outcome.  

Besides, the site admin I mentioned refers to Site collection administrator (A site collection can have several admins). You may suggest the team leader to confirm whether he can manage that site collection and check the permission.

Regards,

Zixuan

Answer 5

Because a #VALUE! error in Excel usually says, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." The latter reason is more suitable for your situation according to the error message. (Although you have that file saved locally, the fomula still referrs to the file saved in SharePoint.)

If the users in Marketing team encounter the same error message, please contact the site admin to check the permission of those files and confirm whether he has made some changes for that site. 

Regards,

Zixuan

I just remember that the Marketing user told me that previously there is actually formula present ("=K39+M39" for example) in the box instead of "https:/***.sharepoint.com/...". Is it because the files are uploaded to SharePoint that makes them unusable anymore? Does the site admin refers to the team lead (owner of the file)?

Thanks in advance.

Regards.

<PII has been removed by forum moderator>