Merge multiple columns into a single column on another sheet?
Hi,
I have 3 columns on the same sheet (C1, C2 and C3) and I want to Merge them into a single column "A1" on another sheet.
Is this possible in Smartsheet and how can I proceed?
Thank you for your help.
Best,
Answers

If you have the same number of rows in each column all the time and the values just change you can just set up a cell reference. If the number of rows in each column change I have not been able to find a way to move or copy specific column information into another sheet. That would definitely be a handy feature.

Thanks for your help. Yes, the number of rows is the same all the time, only the number of rows is continuously updated. So, how can I handle this? Thanks in advance.

Since the number of rows is the same all the time go to your second sheet and just do an =If statement that references only the cell and not the column you want to move over.
=If(Value 1 cell="","",Value 1 cell) you can't just copy and paste the formula you will have to set up the cell reference. Do the same formula in each of your cells on your new sheet referencing the cell you want to bring in. Anytime the value is updated on your reference sheet it will bring in the new value on your new sheet.

Thank you @Hollie Green for your help
best,

Hi,
How would this work if the number of rows is changing?
Thanks!

Hi @RRR ,
Simplifying your problem, I developed a solution in a single sheet as screenshots below :
Follow these steps:
1 Add a new "No" column and populate numbers in increasing as much as you want.
2 Add sheet summary fields #C1, #C2, #C3 are number of nonblank elements in each column with formula
#C1 = COUNTIF([C1]:[C1], @cell <> "")
#C2 = COUNTIF([C2]:[C2], @cell <> "")
#C3 = COUNTIF([C3]:[C3], @cell <> "")
3 Add a new "ID" column with formula :
=IF(No@row <= [#C1]#, No@row, IF(No@row  [#C1]# <= [#C2]#, No@row  [#C1]#, IF(No@row  [#C1]#  [#C2]# <= [#C3]#, No@row  [#C1]#  [#C2]#)))
4 In A1 column, use this formula:
=IF(No@row <= [#C1]#, INDEX(COLLECT([C1]:[C1], [C1]:[C1], @cell <> ""), ID@row), IF(No@row  [#C1]# <= [#C2]#, INDEX(COLLECT([C2]:[C2], [C2]:[C2], @cell <> ""), ID@row), IF(No@row  [#C1]#  [#C2]# <= [#C3]#, INDEX(COLLECT([C3]:[C3], [C3]:[C3], @cell <> ""), ID@row))))
This is a general solution, regardless of whether the number of rows in the C columns is the same or not.
Try it and see if it works for you.
Gia Thinh Technology  Smartsheet Solution Partner.

@Gia Thinh wondering if you could help me with a similar formula. I am trying to collect Planned and Unplanned absent into one column in separate rows.

Disregard, I was able to figure it out with:
=IF(Helper@row <= [Planned]#,
INDEX(COLLECT([ABSENT PLANNED]:[ABSENT PLANNED], [ABSENT PLANNED]:[ABSENT PLANNED], @cell <> ""), ID@row),
IF(Helper@row  [Planned]# <= [Unplanned]#,
INDEX(COLLECT([ABSENT UNPLANNED]:[ABSENT UNPLANNED], [ABSENT UNPLANNED]:[ABSENT UNPLANNED], @cell <> ""), ID@row)
)
)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!