Conditional Formatting using Formulas/Column Reference

Martin Suchy

Hi - is there any way to add formula/column reference into the conditional formatting as I am not able to add any reference to cell/column/formula now? I am getting inputs from different sheets into a final one where I would like to see those cells which are wrong eg. in red automatically, using conditional formatting.

Thank you

Kelly Moore

Sorry Martin, I immediately thought you meant computational or complex logic conditions that are outside of the conditional formatting filtering ability.

In your example above without a helper column you could:

• look for specific text in a column using the Condition CONTAINS and inserting the word(s). You'll need a separate rule for every OR condition. For instance, you could look for 'TBD'.
• Indicate Date + 2 days using the criteria In the Next 2 days. This could work for your green condition but not red.

I have not found a way to compare the date of one column to another without helper columns- I add a formula to this helper to check a box or yield "green" or "red", or maybe the duration of days between dates, whatever works for my specific need. In the conditional formatting, I would then choose the helper column as the trigger for the condition, then in the Format section of the conditional formatting, I would choose the columns that needed to be colored. [In your case, I might use Difference between Requested and Start Dates as a helper column. With one helper, I could set conditions when value <

If you need additional info/refresher on conditional formatting, look here

Does this make sense? If you need any help building logical formulas, I'm sure  @Bassam.M Khalil and I , and the rest of the community, would be glad to help you out.

Kelly Moore

Hi Martin

No. You cannot directly add formulas as a condition in conditional formatting. The work around is to add a helper column, like a checkbox column, that meets those conditions. Use the checkbox column to trigger the conditional formatting.

Bassam Khalil

Hi @Martin Suchy ,

Hope you are fine, could you please add a screenshot showing your final sheet ( remove any sensitive data )

Martin Suchy

Hi @Bassam.M Khalil @KDM

here is the example of what I would like to get.

I insert Start Date Requested and Start Date/End Date is taken from other sheets based on the unique ID which I am also inserting. Cells in green are ok and meeting criteria: start date=start date requested, end date=start date requested+2 days. Red ones do not match.

Other example is that I'd like to set up conditional formatting as eg. if column TBD includes a specific text which is in column TBC, then it gets green, otherwise red.

So if I add helper columns for all the calculation I need to run in the background, is there any way how related cells can be formatted based on that?

Thank you

Kelly Moore

Sorry Martin, I immediately thought you meant computational or complex logic conditions that are outside of the conditional formatting filtering ability.

In your example above without a helper column you could:

• look for specific text in a column using the Condition CONTAINS and inserting the word(s). You'll need a separate rule for every OR condition. For instance, you could look for 'TBD'.
• Indicate Date + 2 days using the criteria In the Next 2 days. This could work for your green condition but not red.

I have not found a way to compare the date of one column to another without helper columns- I add a formula to this helper to check a box or yield "green" or "red", or maybe the duration of days between dates, whatever works for my specific need. In the conditional formatting, I would then choose the helper column as the trigger for the condition, then in the Format section of the conditional formatting, I would choose the columns that needed to be colored. [In your case, I might use Difference between Requested and Start Dates as a helper column. With one helper, I could set conditions when value <

If you need additional info/refresher on conditional formatting, look here

Does this make sense? If you need any help building logical formulas, I'm sure  @Bassam.M Khalil and I , and the rest of the community, would be glad to help you out.

Martin Suchy

hi @KDM

no problem! Thank you, I should be able to cover what I need.