Conditional formatting not working for formulas with reference to another sheet

Lukas.M

Hello

I have an IF-formula in a cell which references values from another sheet. For examle IF(CELL_X from ANOTHER_SHEET = 10, "Ok", "Not OK"). I also have conditional formatting based on the values "Ok" and "Not Ok". However, the conditional formatting does not work reliable. Sometimes the cell is just unformatted. 

The orginal formula is more like:

=IF(COUNTIF(CELL_A:CELL_C, {Metadaten Range 7}) = 3, "abc", IF(COUNTIF(CELL_A:CELL_C, {Metadaten Range 7}) > 0, "def", "xyz"))

The formatting works initially. After saving the sheet, changing the view to another sheet, changing back and after clicking on the cell the formatting disappears. => Seems to be a bug.

Shaine Greenwood

Hi Lukas,

Reach out to our Support team to troubleshoot this further. Our team can work with you to determine whether this is a bug or not.

Thanks!

SaraVP

I am experiencing exactly the same - the conditional formatting used to work without any problems, but since I implemented a reference to another sheet in the formula (it's a workday() formula), the conditional formatting disappears. 

Did you investigate this matter further in the meantime? It is very important that the conditional formatting works for us, since we share our sheet with external partners who need to have a quick overview of deadlines.

celento

I have this same issue. I apply a simple conditional formatting rule. If a cell is 100% then turn the entire row "Green".  I hit apply and it applies and I save. I go out of the sheet then when I come back into the sheet it un-applies the conditional formatting automatically and I have to save.  When I look Activity Log it says "Conditional Formatting Unapplied". 

 

Daniel Stein

Thank you for reporting this. There is currently a known issue where conditional formatting doesn't update consistently for cross-sheet formulas. We are actively working on this and plan to release a fix in an upcoming release. We apologize for the inconvenience.

Best regards,

Daniel

Bladerunner

Hi I'm having the same issue, formatting works as expected until I leave the sheet or refresh it, then the conditional formatting is unapplied but the rules are still there. If I make a slight change to one of the rules, e.g. colour change and then hit OK the formatting for all rules is reapplied. I am using a COUNTIFS statement referencing another column in a different sheet with a drop-down list of four words. It was working OK yesterday with a COUNTIF statement that was returning a total of checkboxes ticked. I can see it actually remove the formatting when I load the sheet. Is there an update on this?

Kind regards.. Ben.

UPDATE The rules work in the mobile android app and also when using the app through a browser on my phone. This appears to only happen when Im accessing the sheet through my pcs browser. Could this be browser cache related?

Daniel Stein

Thanks again to the folks who reported this. We have a fix for this issue that we plan to ship next week (currently scheduled for the evening of 3/13). We apologize for the inconvenience.

Bladerunner

Hi Daniel is this still due to go live the evening of the 13/03? Can you tell me what date/time that would be GMT please?

 

Kind regards.. Ben.

Daniel Stein

Yes Ben. Our releases typically roll out across the user base over the course of a day or so, so I can't give you a precise time, but you should see this before the end of this week. The earliest you would see it is the early morning of this Wednesday, GMT.

Best regards,

Daniel

Bladerunner

Hi Daniel,

 

many thanks.

 

Kind regards.. Ben.

Amanda Denery

I am experiencing this exact issue. I have conditional formatting set up on a field that is a vlookup from another sheet but it will not apply. 

Andrée Starå

Hi Amanda,

What kind of field type is it?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD