Helper Sheet Help

edited 09/12/22 in Formulas and Functions

@Andrée Starå has mentioned on multiple posts about creating Helper Sheets when trying to manipulte data to have certain rows copied to other sheets. I'm completely stumped where to start with the cross-reference formulas to make that happen.

I have a master sheet with a lot of columns, some of the data in the master needs to get copied to another sheet but not all columns are necessary. I have a unique identifier already in the master and am unable to create another one. How do I pull the needed data into a blank helper sheet that I can have copied to the other sheet when triggered by clicking a checkbox?

Thank you for any assistance!


  • PWNA Sam Harwart
    PWNA Sam Harwart ✭✭✭✭✭

    You can use index matches to reference other sheets. When you are building a formula, there is a popup that says "Reference another sheet" when you get to the arguments.

    You can use the unique identifier that you have in your master sheet to pull in the other columns of data that you need.

    Another option is to move the rows somewhere else and use a report to view only the columns you need.

    These might help:

  • Thanks @PWNA Sam Harwart, I don't have a unique identifier in the new sheet I want to create that I can match with the other sheet.

    The new sheet is basically going to be a "ghost" sheet. I need about 12 of the 40 columns/fields in my master sheet copied into that ghost sheet so setting up the automation to move/copy rows won't work. I'm trying to figure out how to get my ID number (the unique identifier) to automatically go into the new/ghost sheet and then I can index/match the rest of the columns I need.

  • PWNA Sam Harwart
    PWNA Sam Harwart ✭✭✭✭✭

    Is there going to be other new information added into the ghost sheet? If not, I'd use a report.

  • No, but after the info goes into the ghost sheet there will be an automation set up to copy that row to another sheet that has other columns that don't pertain to our team.

    We set up a project management sheet/tracker, some of our projects will need IT to work them. The projects that need IT will go into the ghost sheet then they will get copied to the IT sheet. The IT sheet has fields we don't need and we have fields they don't need. It may come down to us having to copy the entire row and hiding the columns IT doesn't need.

  • PWNA Sam Harwart
    PWNA Sam Harwart ✭✭✭✭✭

    I don't think there is a way to move the rows with only some of the columns (though it'd be a nice feature for them to add). Depending on the volume of projects moving, it might be easiest to just manually input the unique identifier from the master sheet into the ghost sheet and use formulas to pull in data for the columns you're interested in maintaining. You could copy it from there to the IT sheet.

  • I think that's what I'm going to have to do. Thanks for your help!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!