when line data meets criteria, add some line data to new sheet


I have read a lot of the information, but unable to find the right formula for what I am trying to do. Hopefully someone can assist!

I have an 'all stores details' sheet. When 'current store' is checked, I want row data to populate into a new sheet.

EG: If 'current store' in 'All Store Details' is checked, return StoreID as a new row into 'NewSheet'.

Report won't work for this as I need to add new data to the rows.


  • Nicole Finch

    @Scott Peters I had looked into that, but I don't want the entire row data, only specific columns from the row like the StoreID, StoreName, etc. I don't want to bring across all the other columns.

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭

    @Nicole Finch - I understand, and have been on the fence with that as well. One approach is to use the copy rows, and in the destination sheet hide all of the unwanted columns. This is noisy for you as an Admin of the sheet, but it's the fastest approach and keeps it clean for your users. You could also add some automations to your destination sheet that says "when a new row is added (from the copy row automation), then clear the unwanted cells.

    To your point, currently there's no way to copy just a portion of a row, but it's a popular request. When you have a chance, consider submitting an enhancement request

  • Nicole Finch
    Nicole Finch ✭✭
    edited 02/15/23

    @Scott Peters I was hoping there was someway of an Index/match/catch formula that I could apply. But I will have to figure something else out.

    Thank you!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Nicole Finch

    I hope you're well and safe!

    To add to Scott's excellent advice/answer.

    Here's a possible workaround or workarounds

    • You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.
    • Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

    To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!