Conditional Formatting Not Triggering When Total Inventory Is Below Zero

System

This discussion was created from comments split from: Conditional Formatting using Formulas/Column Reference.

Debi.Fisher

I have an "Inventory Rollup" sheet that uses the following formula pulling data from an "Inventory Dataset background sheet, then subtracts any entries from an "Issue Log" sheet that are captured/rolled up in the "# REQ" column:

=SUMIF({E&I Material Inventory Dataset [IDENTS]}, IDENTS@row, {E&I Material Inventory Dataset [TOTAL]}) - [# REQ]@row — bold added to show the two values being calculated

This is all working perfectly - I've confirmed my data values/rollups are accurate.

I've highlighted in green 2 problematic rows. Notice that in row 24, the helper checkbox "TTL < 0" is checked, but the conditional formatting of BOLD/RED is not applied to the font in Total Inventory. I've tried applying it to the cells based on value is <0 and I've tried applying it to the cells based on a checkbox helper column ("TTL <0"), and neither way works.

Here is a screenshot of the conditional formatting, currently using Total Inventory < 0:

But if I change it to the Helper/checkbox column it still doesn't work:

Based on numerous google searches for Smartsheet help regarding this issue, it seems I have this all set up correctly, so I'm baffled why these formulas are not working correctly.

Thanks in advance!

jmyzk_cloudsmart_jp

Hi @Debi.Fisher

I was not able to recreate your problem.

The first one calculates the total inventory referencing the data within a sheet.

https://app.smartsheet.com/b/publish?EQBCT=7a15dafbda2d42208860e7852244248e

I suspected that cross-sheet reference may be related to the issue.

So, the second one calculates the total inventory referencing the data in another sheet.

But, the conditional formatting works as expected.

https://app.smartsheet.com/b/publish?EQBCT=4f1d8107a1c142cd9182d4bcf3abf6a2

One possible cause is that if you have another conflicting rule on the column before the desired one, "Total inventory is less than 0" rule, the one in front will supersede.