Copy all Data from one Column in a sheet to another column
Hi ,
I'm trying to copy all the information from one Column to another Column in a different smartsheet.
Can I use Index for this? I see example of index/match but I don't have anything to match.
Example
Sheet name "Testing" Has several columns one of which is named "ITEMS"
Sheet name "Testing 1" Has several Columns one of which is name "LIST"
I want whatever is in sheet "Testing" column "ITEMS" row "1" to appear in
Sheet "Testing 1" column "LIST" row "1" and then turn it into a Column formula so it will work all the way down.
Bonus if the formula shows blanks instead of some other error message, if the Cell in the source column is empty.
Best Answer
-
You shouldn't need a unique identifier on the source sheet if you are wanting to pull everything over exactly as is.
On the second sheet you can use a text/number column (called "Number" in this example) and manually enter the numbers 1 through however many you need. I suggest also including some extra space as a buffer.
Then you can use this as a column formula:
=IFERROR(INDEX({Column To Pull Over}, Number@row), "")
Answers
-
Would it be possible to use a report instead of a separate sheet?
-
A report won't work because I want the information in another sheet that I can then use for additional things.
It seems odd this is so difficult you can do this inside the same sheet with just = column name @row.
Is it possible to set up an index/match that will just match anything?
-
Hi @Fboivin
I hope you're well and safe!
To add to Paul'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.
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!
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.
-
@Andrée Starå That might work can you give me an example of how to do it?
As I said in my original question if Index/Match is the way to go my question is how do I set that up when I don't have any matching criteria other then it is in the same row on the source sheet as the destination sheet
-
Excellent!
You'd add an auto-numbering column in the Source sheet and then add the same number sequence in as many rows as you need in the Destination sheet. Then when a new row is added in the Source, it will populate automatically in the Destination.
Make sense?
Would that work?
✅Remember! 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.
-
I'm not quite following.
Can you show me the formula that would make this "Then when a new row is added in the Source, it will populate automatically in the Destination." work?
-
You shouldn't need a unique identifier on the source sheet if you are wanting to pull everything over exactly as is.
On the second sheet you can use a text/number column (called "Number" in this example) and manually enter the numbers 1 through however many you need. I suggest also including some extra space as a buffer.
Then you can use this as a column formula:
=IFERROR(INDEX({Column To Pull Over}, Number@row), "")
-
Thank you Paul that worked perfectly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 68K Get Help
- 474 Global Discussions
- 209 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 85 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!