Welcome!

Conditional formatting not working for formulas with reference to another sheet

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.

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

I am getting an error message when attempting to export to excel
I am attempted to export to excel. I have two different smartsheet documents to export. One of them exported to excel fine, but not the other. This happened a few weeks ago. I make sure all columns were the correct size & no cells were hidden. "We have found a problem with (file) Do you want us to try and recover as much…
Need Help w/ Report View - Nothing Is Showing
Hello Smartsheet Community, I need some help with a report that I created and shared with a customer. The customer is not seeing any of the information that is pulling into the report. See screen shot below that the customer texted me. I do have the report filter only showing the customer their product pulling from our…
How can I hard code a cell based on a trigger?
BLUF: When I create an Invoice, I need the cell-link to my live price data to be severed. I currently have a Price sheet for my products that is updated frequently. I use Control Center to create Projects, which includes an Invoice sheet. I have a dropdown on my invoice sheet to select a product, and then I use Index…