Clear a row based on a different sheet

sanscritt

I am trying to use a seniority list as the key to modify other sheets. Currently when we have a new hire I am able to copy that information to a training matrix sheet and an attendance sheet.

I want the seniority list sheet to be the catalyst so only one sheet needs modified.

How can I set it up to clear or delete a row when someone leaves? if we delete (or check a term checkbox) can I set that up to clear the contents on the other sheets?

Jeff Reisman

You can clear cells automatically based on a value in a cell from another sheet; however, the change of that cell value cannot be the trigger for this automation, you must use time-based or recurring scheduled automation (think: Every hour, check for rows with this value in this cell, and if found, clear the values in these other cells.) There is no automated row deletion, however, and you can't clear system columns (such as created, auto-number, etc) or formula columns.

https://help.smartsheet.com/articles/2482382-clear-cell-value-action

Note: It may work to update a cell via formula when another cell changes to a particular value via lookup, and have that update be the trigger for the clear cell automation. I have gotten this to work, but another user on here used the same formulas, data, and automation and it would not work for him.

For example: I have an INDEX/MATCH in Column B that looks for a data value from another sheet. In Column C I have a checkbox column with a formula that says IF Column B = "Completed", check the box. Then my automation says "When a row is changed, when Column C changes to checked, clear the value of columns D through G." This may work, might not, you'd need to try it.