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

MattMattMatt

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

Amber Eakin

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!

https://help.smartsheet.com/articles/2480316-available-symbols-in-symbols-column

MattMattMatt

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

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.

MattMattMatt

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

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)