May 03 2018
- last edited on
Jul 25 2018
We use Excel web parts to display charts in SharePoint online. Our web parts were working fine for months but now don't show bars due to the reference cell formula not working. When attempting to open the spreadsheet in the Excel browser we get warning about features not supported and if we want to create an editable copy. If we say yes, we can then go to the reference cells and see the formula displaying #REF in the cell. However, there doesn't seem to be an issue with the formula because if after clicking in the cell and then hitting enter the #REF goes away and the formula renders the value. After saving the 'editable' copy the Excel web parts is fixed and the charts show up as they should.
So my question - can features that aren't supported in Excel web app hinder a web part from displaying data that may rely on the feature? Like a macro? We can't quite pin down the issue so any insight would be helpful. Has something changed recently in O365 that might have had an impact on how our spreadsheets work?
May 06 2018 10:57 PM
May 07 2018 07:18 AM - edited May 07 2018 07:25 AM
The formula is below, which for some reason wants to be on two lines. If the formula is on one line, it doesn't work:
I checked this morning and the spreadsheet I fixed on Friday is broken again. The references worked okay until the spreadsheet was modified, then the formula shows #REF again. It is looking at the date field next to it.
The spreadsheet is using table names. Here's a snip of the cells in Excel web app and the Table name. If the spreadsheet is opened locally in Excel, the formula displays okay.
May 07 2018 07:41 AM
May 07 2018 08:42 AM - edited May 07 2018 08:49 AM
I checked out the formula. Once I took the space out of the the column name of the other cell, which had part of the name on another line, the formula now displays all on one line, but it is still not working.
May 07 2018 09:05 AM
May 07 2018 11:35 AM - edited May 07 2018 11:46 AM
Here is the file provided by the document owner.
May 08 2018 01:30 AM
May 08 2018 05:37 AM
I know! That's part of the problem! The formula works locally, but not in SPO. Load the file into SharePoint online and open it in the Excel web app, the formula doesn't continue to work. We get the formula to work and all is good and then it stops working, which I believe is associated with the Excel filing being modified in some other part of the file.
Can you please load the file into a SharePoint site library and verify things there? Add n Excel Web Part to a page, set the name to 'Charts AllTasks' to see the chart. This chart stops displaying the bars when the #REF problem occurs. Modify the file and see if the formula continues to work. I've included a pic of a working chart/Excel formula and a broken blank one.
Thanks for helping!
May 08 2018 09:04 AM
Yes, if open your file in Excel Online it'll be #REF in CALCULATED YEAR/MONTH fields. Desktop version shows the fields correctly.
I applied General format to above columns and re-enter exactly the same formulas. It looks like it works now both in online and desktop versions, please check attached file.
May 08 2018 09:22 AM
I downloaded the file you provided and uploaded it to our SharePoint site. I set-up the Excel web part to use the new file. The charts/formulas worked until I modified another cell in the spreadsheet. It is broken again after saving the changes. No bars and #REF in the formula fields.
May 08 2018 09:39 AM
1) Same columns with #REF (calculated year/month) or any other one?
2) Are your regional settings are the same for your Sharepoint site and desktop? (on the other hand shall not affect text fields)
3) We speak about Sharepoint online with Office365 or that's Office Online Server integrated with Sharepoint 2016?
4) Or that's not Excel Online but Excel Web part within on-premises Sharepoint?
May 08 2018 09:43 AM
Sergei, in response to your questions:
1) - yes the same columns show #REF as before (calculated year/month
2) - yes, same regional setting for local as for SharePoint O365 site
3) - this is related to SharePoint online with O365
4) - this is Excel Online and an Excel services web part in SharePoint O365
May 08 2018 10:52 AM
Carlene, I played a bit more and can't reproduce that. I have no Excel Web part - we are on modern pages, only Excel Online.
- downloaded your file as it is and save on hard drive without any changes
- make a copy of the same file and change columns format on General
In Excel desktop both show no errors
- upload both files on ODFB and opened them in Excel Online
- first file shows #REF, second one has no errors
- tried to edit second file in Excel Online, no problems with it
Both files are attached here
May 09 2018 07:55 AM
The file that is labeled 'format changed' is working and continued to work after editing the file in Excel online. This appears to be our solution. Can you please provide the details on the format change that you did?
May 12 2018 04:54 AM
Sorry for the delay with answer, was on business trip.
Select both your columns - stay on C5, press and hold Shift, right arrow (C5 and B5 will be selected), in addition press and hold Ctrl, down arrow.
Release all keys, after that Ctrl+1. Select format here
Edit C5 and B5 to re-apply formulas.
May 14 2018 07:50 AM
Thank you for the explanation but the document owner doesn't think this is the problem:
It is not a formatting issue, it is a reference issue. Sergei's solution only works because he keeps using excel online. Excel online can't deal with "structured cell references". It has to have "explicit cell references". Sergei's solution doesn't stand the test of the spreadsheet being edited both in excel online and normal excel. I tried and tested it multiple times. It used to work. Something changed that is not in our control, but may cause us a ton of problems down the road. Isn't this something that should be escalated to Microsoft?
Can you please respond to her concerns?
Thank you - Carlene