Move a row to a new sheet based on an entry on a separate sheet


My company has a sheet titled active employee roster which has every employee in the company. We are trying to create a new Offboarding sheet that will then find the employee on the Active Employee roster sheet and then move them to a 3rd sheet labeled Offboarding Archive. I am struggling to create a solution for this problem. I am thinking a combination of index matching and automations would make it work, but I have not quite figured out the solution yet. Does anyone have suggestions?


  • If I understand your use-case correctly (feel free to correct if I don't) - you want the following to happen:

    • You have a list of active users
    • You have a different list of users who should be deactivated (the Offboarding sheet you mentioned)
    • You have an archive sheet of users (Offboarding Archive) where user rows should get moved

    This should be fairly simple to implement, and might even be possible without using the Offboarding sheet (depends on use-case).

    In essence, I'd start with creating a checkbox column on the Active Employee sheet that indicates that the user needs to be offboarded (maybe call it Offboarding Required). That value can either be set manually if desired, or via formula from your Offboarding sheet - you had mentioned Index/Match, personally I'd probably use something like Countifs() or Count(Collect()) as you really don't need to return any information, just whether or not that user exists in that other table.

    Then you can set up the automation to be triggered by Offboarding Required changing to Checked which will move the row to the Offboarding Archive sheet. You can always build in safety via row filters or a delay in order to avoid accidentally offboarding employees.

    Hope this was helpful and gets you moving in the right direction! Happy to elaborate further if you need.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!