How to automatically create a flag based on text criteria in row

Options

Hi there,

My team is trying to integrate Smartsheet into their workflow. They are moving from a excel based contract rights tracking system into the Smartsheet ecosystem. Traditionally they've been using a legend and color coding row data for cell values that are 'TBC' (to be confirmed), I've been playing around with the conditional formatting to identify the specific text to highlight the cell, but the sheet contains many columns that could have TBC values. Is there a efficient way to create a formula to search a row that contains TBC text in the cell and flag with a symbol?

Thanks,

Matt

Answers

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Options

    Hi @MattMattMatt - It sounds a Symbol column would work for you. You could create a symbol (possibly an actual flag!) based on a formula. For instance, if you were using a checkbox or flag, you might use this formula (based on your actual sheet's column names): =IF(CONTAINS("TBC", Primary@row), 1, 0)

    Then, you could have conditional formatting to highlight the entire row or a specific cell based on if the Symbol is checked, not checked, or a specific value.

    I hope that helps!


    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • MattMattMatt
    Options

    Thanks @Amber Eakin !

    I'm still new to formula creations in smartsheet - can you please clarify where I should insert the column names in the formula? Is it the Primary@row portion? Say if I have 20+ columns, would I have to manually add each column?


  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Options

    Hi @MattMattMatt - The "Primary@row" was just a placeholder in my example. You would use whatever column you're searching. Are you saying that you would be looking for "TBC" in multiple columns, and the row would need a flag if it's listed in any of them?

    Can you mark in that screenshot the columns that might have "TBC"? I can help you create a formula once I know what all columns we're searching.

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • MattMattMatt
    Options

    Hi @Amber Eakin - Ah gotcha regarding the placeholder I suspected that. Your assumption is correct regarding what I need with the TBC in multiple columns = row flag. Since Smartsheet cannot filter by fill color I need a workaround. The below are the column names:

    Production Type

    Hero Channel

    Episode Order

    Duration(mins)

    Start Date

    End Date

    Number of Runs

    Free

    Cable

    Pay

    PPV

    Replay/Restart

    R/R Originals/Rpts

    Replay Window

    Livestream Web/App

    Stream - Online

    Stream - Mobile

    Stream - App

    Livestream – vMVPD

    Livestream – DTC

    Livestream – Authentication Required

    Livestream – Dynamic Ad

    VOD Web/ App & STB

    VOD - Online

    VOD - STB

    VOD - Mobile

    VOD - App

    VOD Permitted Channel(s)(Spec vs CTV)

    VOD Window/ Stack

    Online Window/Stack

    STB Window/Stack

    Mobile Window/Stack

    App Window/Stack

    VOD Original/Rpts

    VOD Authentication

    VOD Trigger

    VOD vMVPD

    VOD DTC

    Download to Go

    3rd Party VOD

    FAST

    SVOD

    SVOD Window/Stack

    French

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Options

    Buckle up, @MattMattMatt , this is going to be a huge formula! Fingers crossed that it works.

    =IF(OR(CONTAINS("TBC", [Production Type]@row), CONTAINS("TBC", [Hero Channel]@row), CONTAINS("TBC", [Episode Order]@row), CONTAINS("TBC", [Duration(mins)]@row), CONTAINS("TBC", [Start Date]@row), CONTAINS("TBC", [End Date]@row), CONTAINS("TBC", [Number of Runs]@row), CONTAINS("TBC", Free@row), CONTAINS("TBC", Cable@row), CONTAINS("TBC", Pay@row), CONTAINS("TBC", PPV@row), CONTAINS("TBC", [Replay/Restart]@row), CONTAINS("TBC", [R/R Originals/Rpts]@row), CONTAINS("TBC", [Replay Window]@row), CONTAINS("TBC", [Livestream Web/App]@row), CONTAINS("TBC", [Stream - Online]@row), CONTAINS("TBC", [Stream - Mobile]@row), CONTAINS("TBC", [Stream - App]@row), CONTAINS("TBC", [Livestream – vMVPD]@row), CONTAINS("TBC", [Livestream – DTC]@row), CONTAINS("TBC", [Livestream – Authentication Required]@row), CONTAINS("TBC", [Livestream – Dynamic Ad]@row), CONTAINS("TBC", [VOD Web/ App & STB]@row), CONTAINS("TBC", [VOD - Online]@row), CONTAINS("TBC", [VOD - STB]@row), CONTAINS("TBC", [VOD - Mobile]@row), CONTAINS("TBC", [VOD - App]@row), CONTAINS("TBC", [VOD Permitted Channel(s)(Spec vs CTV)]@row), CONTAINS("TBC", [VOD Window/ Stack]@row), CONTAINS("TBC", [Online Window/Stack]@row), CONTAINS("TBC", [STB Window/Stack]@row), CONTAINS("TBC", [Mobile Window/Stack]@row), CONTAINS("TBC", [App Window/Stack]@row), CONTAINS("TBC", [VOD Original/Rpts]@row), CONTAINS("TBC", [VOD Authentication]@row), CONTAINS("TBC", [VOD Trigger]@row), CONTAINS("TBC", [VOD vMVPD]@row), CONTAINS("TBC", [VOD DTC]@row), CONTAINS("TBC", [Download to Go]@row), CONTAINS("TBC", [3rd Party VOD]@row), CONTAINS("TBC", FAST@row), CONTAINS("TBC", SVOD@row), CONTAINS("TBC", [SVOD Window/Stack]@row), CONTAINS("TBC", French@row)), 1, 0)

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!