Add/Delete certain columns of rows based on criteria



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?



  • Scott Orsey
    Scott Orsey ✭✭✭✭✭
    edited 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!

    If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

  • Jim08

    Unfortunately I'm not familiar with IF() and MATCH(). Do you have any resources that break it down and make it easy to understand?

    Thank you for your help

  • KPH
    KPH ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!