How to automatically create a flag based on text criteria in row
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
-
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
-
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?
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!