why showing #value= when creating external references in excel at Office 365 Sharepoint

Question

I am sharing various files at Share point office 365. Have one file where created SUMIF formula to pull some numbers into this excel file from another excel that is location at the same share point. however when I close excel where number are coming from my formula cell turns into "#value=" error.

Any advice what I can do to solve this?

Answer 1

Hi Sofia,

There are some reasons could cause the #VALUE error. 

1. The formula refers to cells in a closed workbook
2. The criteria string is more than 255 characters
3. In SUMIFS, the criteria_range argument is not consistent with the sum_range argument.

You could get more of them in this article: How to correct a #VALUE! error in the SUMIF/SUMIFS function.

In addition, we suggest you like to the SharePoint file using Power Query. H You could try the steps shared by Tisky in this thread: Linking Two Excel Worksheets in SharePoint Online

If you could make sure that all three reasons are not the cause, we might need more information to provide further support. Please check the questions below:

1. How do you link to the file in SharePoint Online?
2. Could you share a screenshot of the formula you are using?

Regards,

Alex

Answer 2

Thank you so much for taken time to respond Alex. Your directs were very helpful. I was able to solve the issue! Thanks a bunch ones again!

Answer 3

Hi Sofia,

Haven't heard from you for a while. Is there any update? If you still need further support, welcome to reply any time.

Best regards,

Alex