Conditional Formatting Showing #Value for Indiscriminate Users

[Guybrush Threepwood]

Hi all.

I've setup copies of the same Excel spreadsheet template for a number of different users in Office365 online.
The workbook has conditional formatting in a particular cell with an icon and number displayed based on values in other cells.

The weird thing is that when some users open these workbooks up online the conditional formatting is displayed correctly, however when some other users open them up online the conditional formatting displays a #Value error.
What makes it more perplexing is that when the users that get the #Value error open the workbook up from their desktop the error no longer occurs...

I can't trace any pattern as to why this could be occurring and was hoping someone here might be able to help?

Many thanks.

 

[6StringJazzer]

Conditional formatting will not show #VALUE! in a cell. That is the result of whatever formula you have in that cell. What is the formula in that cell?

 

[Guybrush Threepwood]

Thanks, at least I can rule out the Conditional Formatting as the issues then.
The formula is a simple one: =IF(SUM(BA15:BK15)=0,"",SUM(BA15:BK15))
The formula does reference some blank cells, but I don't believe this normally matters?

I also notice when I initially load up the workbook online the cells will show #VALUE! for a few seconds before then displaying the correct result, but not so for other users where they stay as #VALUE!

 

[Peter_SSs]

Are you sure that BA15:BK15 does not contain one or more cells that themselves contain a #VALUE! error (& perhaps those errors are hidden by conditional formatting?)?

 

[6StringJazzer]

I agree with Peter's line of reasoning. Any cell with a #VALUE error in the range BA15:BK15 will cause your whole formula to return a #VALUE error. (Blank cells are ignored by SUM.)

 

[Guybrush Threepwood]

Hi all.
Can confirm there are no errors in BA15:BK15.
The fact it is working for some and not others indicates it may be a glitch, right?
So odd that I can Teams someone and watch them open it in their browser and see the error, yet when I am looking at it on my computer at the same time there is no error... :/

 

[6StringJazzer]

Are they opening the same file, shared online, or are they opening their own copy of it?

 

[Guybrush Threepwood]

Are they opening the same file, shared online, or are they opening their own copy of it?

They are opening a shared copy. I have created the document in OneDrive and then created a link to it on our server.

 

[6StringJazzer]

Does the file have any formulas with external references? (I feel like I'm on some kind of game show.)

 

[Guybrush Threepwood]

No link to external references.
It seems that everyone who access the workbook online doesn't have the conditional formatting as well as some of the calculations as there are other cells that do not have a formula which highlight red for me when I open the workbook online but when others share their screen with me I can see that those cells are not highlighting red for them.

It just seems like a glitch in using Excel online. It seems it has a way to go before being reliable.