Copy one column to new sheet as rows are added

ryanthompson
edited 02/09/24 in Smartsheet Basics

I'm looking to have an automated way to copy a single column to another sheet as new rows are added. I have a unique ID number for each row and want to copy that unique ID to a second sheet for other purposes without having to do it manually. Any ideas?

Answers

  • KPH
    KPH ✭✭✭✭✭✭

    Hi @ryanthompson

    You can create an automation to copy the entire row to a new sheet when it is added. You can hide all the columns that you do not want. You cannot copy just one cell to another sheet.


  • That’s what I was going to initially do but wanted to see if some kind of vlookup like formulas would work. The caveat being I want to pull all new entries regardless of what they say.

  • KPH
    KPH ✭✭✭✭✭✭

    You could use a VLOOKUP or INDEX MATCH to bring a column from one sheet into another but you will need an additional column for it to lookup or match against. This method is also not static. If the original sheet changes, the value you are referencing via VLOOKUP or INDEX MATCH will also change. Whereas a Copy is a one time, static, copy of the data at the time the row was added. So, which route you go, depends on your need.

    If you want to go the lookup route, I suggest INDEX MATCH as it will give you more flexibility. You could create an autonumber column as the thing to match on in the first sheet (this is a column format option). This number will increase automatically as each new row is added. You would need to add this manually to the second sheet (you could set up hundreds of numbers all in one go and won't have to do this one at a time), along side a column with your INDEX MATCH formula.

    Does that sound like what you need?

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

    Hi @ryanthompson

    I hope you're well and safe!

    You could connect the sheets using cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure. 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 could 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 need in the Destination sheet.

    Another option could be only to use the INDEX feature to get specific rows of data.

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

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