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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!