Contains function - leave unchanged unless condition is met
I need to update cell A in each row of a very large sheet only if a certain substring exists in cell B in the same row. If the substring does not exist in cell B, leave the existing value in place, do NOT blank it out. I cannot use any alternative "else" assignment approach because these cells are already populated. In practical terms, I need to update the assignment of 100s of specific tasks to a NEW (the same) person without having all the other task assignments becoming blank.
Answers
-
When you say you need to update "100s of specific tasks to a NEW (the same) person", do you mean that all cells that are to be overwritten will be overwritten with the same value?
Is this a one time thing, or is this something that will stay with your sheet going forward?
I would start by adding a new column(checkbox) to identify rows that need changed and setting a column formula to fill it:
=IF(CONTAINS("substring", [Cell_B]@row), 1, 0)
You can then set an automation to change the values in Cell A of the relevant rows. If you only need to run this once, you can set it to run on a specific date (set a future date) and then simply run it manually or deactivate/delete it after use.
This only works if all cells that meet the criteria need to be overwritten to the same value. I hope I understood your question correctly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 201 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!