Add/Delete certain columns of rows based on criteria

Jim08 · 03/29/24

Hi,

I'm fairly new to smartsheets. I'm trying to have automation copy/delete certain columns from from sheet 1 to sheet 2. I've created an example below to give context what I want to do. The order status column is a dropdown so whenever rush is displayed I want the Employee, Order Number, Cost, and Order Status columns copied to sheet 2. If Rush is deleted in sheet 1 I want the entire row deleted in sheet 2 but ONLY if it's deleted in sheet 1. If something is manually into sheet 2 I don't want that deleted. If test 8 is entered into sheet 2 and someone accidentally enters it in sheet 1 I don't want it deleted. Is there any way I can accomplish this?

Thanks

Scott Orsey · 03/29/24

HI @Jim08, Welcome to SS. You can definitely accomplish what you'd like. I'll give you a thumbnail of a few ideas and then you can dig in a little bit. If you have further questions, feel free to reach back out:

To copy rows based on your dropdown value, look into setting up an automation that fires based on cells changing on a row. Then you can use the copy rows feature. Note that you can only copy entire rows. If the destination sheet doesn't have some columns in your source sheet, SS will automatically create these columns. In most cases this isn't a problem... you can hide unwanted columns or send them off to the far right and if you create a report, only select the columns you need. You can add additional columns into the second sheet without it affecting the first. Those columns will just be blank if they don't contain a formula.

To "delete" a row in your second sheet: First off, you can't delete rows, but you can move them. What folks typically do is create a "Trash" sheet. Then, I recommend creating a checkbox column with IF() and MATCH() which will look for your order number in your first sheet (using a cross sheet reference). IF() and MATCH() work very similarly to the way they do in Excel if you are familiar with that. Then, just like I suggested above for copying the row, you will want to create an automation that looks at your checkbox. In this case you will want to move the row to your Trash sheet.

To address the situation where someone enters something into sheet 2 that isn't in sheet 1, you will just need to improve the conditions within the IF() formula for the checkbox. You'll need some way to know if it was entered in sheet 2 manually. So, the question is, how do you expect people to do that? If you are using a form, then you can create another column, maybe a checkbox, that indicates manual entry. In the form you can set this checkbox's default value and make it invisible so that no one sees it. If instead, you are having people manually enter new rows into sheet 2, you'll need to explore other ways to recognize that it was manual. Maybe something that gets copied from the other sheet would be good to key off of.

Hope this helps. Good luck and be well!