Features not allowed in Excel Web app

[Carlene Bedient]

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?

thanks!

Carlene

[#2]

Can you perhaps share the formula showing the REF error before and after the fix? If there are any range names involved, please include their formulas too.

[#3]

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:

=TEXT([@[Due Date
(Day-Month-Year)]],"mmmm")

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.

Thank you,

Carlene

[#4]

Try editing the header (in the table) of that particular column and remove the line break that is in the name (an alt+enter adds a line break).

[#5]

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.

Carlene

[#6]

Is there any way you can share the file with broken formula? you can remove all irrelevant (and sensitive) information from it.

[#7]

Here is the file provided by the document owner.  

[#8]

The formula works fine when I open the file in my Excel 2016 I'm afraid, so I don't know what else I might do to help :(

[#9]

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!

[#10]

I'm sorry, I have no access to SPO.

[#11]

Hi Carlene,

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.

[#12]

Hi Sergei,

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. 

Carlene

[#13]

Carlene,

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?

[#14]

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

[#15]

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.

My steps

- 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

[#16]

Does it help to remove the alt+enter that is in the title of the column?

[#17]

No, Jan, that did not help.

[#18]

Sergei,

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? 

[#19]

Hi Carlene,

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.

[#20]

Sergei,

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