Copy one column to new sheet as rows are added

ryanthompson

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?

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.

https://help.smartsheet.com/articles/2479626-automatically-copy-rows-between-sheets

ryanthompson

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

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å

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!

jasmine.pena

@Andrée Starå: I have seen you post this same process as answers to multiple scenarios where individuals are needing to copy specific columns into another sheet and I am currently attempting to use your method of helper sheets.

Currently, I have an automation set up on my source sheet that copies an entire row into the Primary helper sheet when a box is checked. This primary helper sheet also has a Auto-number column called "Row ID"

The secondary helper sheet has columns created for only information I would like to show on the final sheet.

I am getting stuck on the next steps: Can you please explain or give a formula example on how I can "use your method as previously described" to get the values I need onto my secondary helper sheet?

I created the "Row ID" column on my primary helper sheet based on your recommendation/instruction "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." But I don't understand what to use in the "so-called helper column" to get this to work. Can you help me understand what to do next?