Conditional formatting not working for formulas with reference to another sheet

Options
Lukas.M
Lukas.M
edited 12/09/19 in Smartsheet Basics

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.

Comments

  • Shaine Greenwood
    Options

    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
    Options

    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
    celento ✭✭✭
    Options

    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
    Options

    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
    Bladerunner ✭✭
    edited 03/07/18
    Options

    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
    Options

    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
    Options

    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
    Daniel Stein Employee
    edited 03/12/18
    Options

    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
    Options

    Hi Daniel,

     

    many thanks.

     

    Kind regards.. Ben.

  • Amanda Denery
    edited 10/25/19
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi Amanda,

    What kind of field type is it?

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD


     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.