Conditional Formatting using Formulas/Column Reference

Options

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

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Martin Suchy ,

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Martin Suchy
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

    hi @KDM

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!