Conditional formatting by comparing two fields with each other?

Options

I see that conditional formatting is possible if a certain field fulfills a certain criteria i.e. contains certain data, is equal to certain data, etc.

But, I would like conditional formatting if my field #scoped devices is equal to my field #shipped devices.

I see that I can move or copy entire rows to another spreadsheet as part of the workflow feature. But I just want to format a row, when the job is completed.

Is that possible? Thanks so much.

Best Answer

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Barbara Witt

    I hope you're well and safe!

    You'll have to add a so-called helper column with a formula that checks if the values are the same and then reference that in the conditional formatting.

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    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.

  • John Ramaker
    Options

    I am facing the same issue.

    So I have to insert a status column every time I want to highlight a value that is not at my desired target? I have 50 columns and I want to be able to highlight values in those columns based on a target column, will I have to insert 50 extra columns?

    In conditional formatting under the 'Set Conditional' dialog box I would like to define my criteria as 'is less than' and then for the criteria enter the name of another column to compare to the 'select column for condition'. Is this possible?


  • Barbara Witt
    Options

    That makes sense, Andree. I added a helper column to calculate the difference and if the difference is zero, then it formats the row. It works. Thank you very much.

    I do have another question for a more tricky formatting scenario. I think I need an "or" criterium, and not just "and" criterium. What could be a potential work around?

    Format if:

    1. difference of scoped to shipped for part A is zero
    2. difference of scoped to shipped for part B is zero
    3. device in scope for part A should be yes and # scoped for part A should not be blank
    4. OR
    5. device in scope for part B should be yes and # scoped for part B should not be blank

    Reason for the OR:

    • # scoped is zero if there is no order (device in scope = no) or if the # has not been entered yet (device in scope = yes, but # scoped is blank)

    Any thoughts? Thanks so much, Barbara