Only copy certain columns to a new sheet?

I am trying to marry two sheets together, one is a new sheet for ticket intake and the other sheet has been in use by another team that has ticket information for one person but also additional information for their team. I don't want the team to have to enter in the data into the intake form in addition to their form. I know I can set up an automation to copy a row from the team sheet to the Intake sheet but the team sheet contains additional information that I don't need on the Intake sheet. Is there a way to set things up so that on the team sheet it's only copying a row with data from columns 1 - 5 and not 1 - 20?

Best Answers

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭
    edited 06/23/23 Answer ✓

    @Pamela Wagner There's nothing in core Smartsheet.

    If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.

    Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message at https://smartsheetguru.com/contact

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Pamela Wagner

    I hope you're well and safe!

    To add to Darren's excellent advice/answer.

    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 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: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.

Answers

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭
    edited 06/23/23 Answer ✓

    @Pamela Wagner There's nothing in core Smartsheet.

    If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.

    Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message at https://smartsheetguru.com/contact

  • Pamela Wagner
    Pamela Wagner ✭✭✭✭✭✭

    Hmm, data mesh. I've not used that and will see what it can do for me. Thanks for now!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Pamela Wagner

    I hope you're well and safe!

    To add to Darren's excellent advice/answer.

    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 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: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.

  • Pamela Wagner
    Pamela Wagner ✭✭✭✭✭✭

    Hi @Andrée Starå hope you are well too! This certainly sounds like an approach as well! I do want to learn data mesh but this might be a good option too as I don't know if you can initiate a data mesh process if you don't have a license (I can set it up but another group has to administer it). Thanks much!

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

    @Pamela Wagner

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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.

  • CAS the CSA
    CAS the CSA ✭✭✭
    edited 07/02/24

    I'm looking do to the same now, if I add a unique ID to a basic info sheet I would like it to create a duplicate row in all of five other sheets that gather input for different reasons, but only populate the ID column, not all the other extraneous details from the initial sheet.

    To circumvent this failing I have a dedicated sheet with one single column that we add new IDs to and that has a workflow that in turn copies the row to six other spreadsheets, including the basic info sheet.

    Unfortunately this is tedious and leads to duplicate-duplicate rows if someone adds a row they need before the process runs. Additionally it is counter intuitive as everyone instinctively starts to add the new ID to the Basic Info sheet and not just add it to the feeder sheet and wait for it to populate a row in the info sheet to add the details.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!