Features not allowed in Excel Web app

I can't replicate it because I can't get webaccess to save it in the weird "Editable.xlsm" format like it did to the other files.

To be sure better to play with the simple file created from scratch, that's few minutes job. Your two existing documents may have the same issue.

It has happened with two separate documents, so I would assume for all.

But again, that's for any Excel file in your environment, or only for this specific one?

It appears to be changing the document in the desktop client that breaks the online version when using structured references.

Can't reproduce that. Is such behaviour for any Excel file in your environment or only for the one we are discussing?

If you create simple file from scratch with the table and structured references formula (in Excel Online or in Desktop client), edit it in Desktop client (e.g add couple of rows plus change the formula with structured reference), save and edit in Excel online (that's what I did with above sample) will you have #REF?

That has not been our experience.  After editing the workbook in the client app, the formulas fail in the web app.  We have tested this many times.  Screen shots before and after are attached.

IMHO, Excel online correctly works with structured references, the only point you shall enter formulas with structured references manually, Excel online doesn't autofill them. It looks like

Data is in table format (using traditional Excel locally).

When you use the local excel application and make a formula that references something in the table boundaries, the formula uses a reference to the table first and then the specific cell (a 'structured reference' to use Excel terms).

Excel online does not understand those references.  In particular it returns a #REF.  In other words it’s saying sorry, I don’t recognize what your formula is referring to.

So then you have to go into excel online to edit the formulas and when you select the cell you need in a formula it gives you a traditional reference like “E5”  (an 'explicit cell reference').

Then the excel online knows what the heck you are talking about and doesn’t return the #REF.

This is a new problem that cropped up in the last few weeks.  We have been using the same spreadsheet successfully for months with a SharePoint webpart.  Suddenly it stopped working and returning the #REF.  With a simple spreadsheet the user can deal with going in and changing the formulas to only use explicit cell references.  With a more complicated spreadsheet that is going to be a very big problem.  

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

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.

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? 

No, Jan, that did not help.

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

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

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?

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