Link an entire column from 1 sheet to another

cvc.lukes

I know you can do this at the cell level, but is it possible to link an entire column? I'm trying to save the time of linking over 6000 cells and want the ability to grow as I know the numbers will be increasing over time.

Paul Newcome

If you are manually adding the row number to the destination sheet, then you really shouldn't have to use the auto-number or anything additional on the source sheet. You can just use a cell reference to refernce the column housing the row number on the destination sheet.

=INDEX({Other Sheet Column A}, [Row #]@row)

Using this method, you would either need to add new rows to the destination sheet as new rows are added to the source sheet, or "pre-fill" rows in the destination sheet so that you have more than enough rows for expansion ready to go. Then you can use an IFERROR to leave empty, pre-filled rows blank until they are added to the source sheet.

=IFERROR({Other Sheet Column A}, [Row #]@row), "")

Andrée Starå

Hi @cvc.lukes

I hope you're well and safe!

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.

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Have you looked into using a report?

You can actually cell link multiple cells all at once so long as the size and shape of the ranges match.

For example, go to a column in the destination sheet and highlight 10 cells going down that column. Right click, select the option to link to another cell, select your source sheet, then select any 10 cells that are right next to each other within the same column as each other.

All 10 cells will be linked at the same time, and you only had to go through the linking process once.

cvc.lukes

@Andrée Starå I thought about using a VLOOKUP or INDEX, but I'm not matching it to anything. What I'm looking for is basically Column A in Sheet A is Column A in Sheet B.

@Paul Newcome The report gives me the ability to mirror the columns but then I need to do data manipulation to those columns and I don't see the ability to add additional columns or the ability to do any sort of formula's off of the columns added.

At a high level what I'm trying to do is, take 2 spreadsheets and combine them. 1 is a spreadsheet of all users and the other is a list of users I need to test. I want to be able to show from all users who is requiring testing and then I'll be adding in results of the testing once completed.

Andrée Starå

@cvc.lukes

Happy to help!

You could add an auto number column as the unique ID in the source sheet and then manually add the numbers in the destination sheet, and that would keep them synced.

Make sense?

Would that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

If you are manually adding the row number to the destination sheet, then you really shouldn't have to use the auto-number or anything additional on the source sheet. You can just use a cell reference to refernce the column housing the row number on the destination sheet.

=INDEX({Other Sheet Column A}, [Row #]@row)

Using this method, you would either need to add new rows to the destination sheet as new rows are added to the source sheet, or "pre-fill" rows in the destination sheet so that you have more than enough rows for expansion ready to go. Then you can use an IFERROR to leave empty, pre-filled rows blank until they are added to the source sheet.

=IFERROR({Other Sheet Column A}, [Row #]@row), "")

Andrée Starå

@Paul Newcome

Excellent tip! 👍️

I'm so used to INDEX/MATCH that I didn't think of not using MATCH.

cvc.lukes

Thanks @Paul Newcome this will work!

Paul Newcome

@cvc.lukes Happy to help. 👍️

@Andrée Starå I catch myself doing the same thing quite a bit. Fortunately I had just earlier today set something similar up and changed it back after questioning the redundancy of it, so it was still fresh on my mind. Haha

Paul G.

@Paul Newcome Like cvc.lukes I am wanting to have the contents of one column appear in a column of a second sheet. I have attempted to use the two formulas (=INDEX({Other Sheet Column A}, [Row #]@row) and =IFERROR({Other Sheet Column A}, [Row #]@row), "")) you provided. I have tried a number of variations which result in the unparseable error. Could you please provide some assistance with the formula.

I have named the source column in sheet 1 "SurveyCommentRng". The formula will appear in a column in sheet 2. The information begins on row 1 of the source column. The formula will begin on row 1 of sheet 2. In case this effect the formula or the type of formula needed sheet 1 will be filtered from time to time.

Thank you in advance for your help.

Paul

Paul Newcome

@Paul G. Did you use the helper column for [Row #]? Are you able to provide screenshots? Can you copy paste the formula that gave you the error from the sheet to here?

Paul G.

@Paul Newcome Thank you for responding. When you say helper column I think of Excel and a helper column as a column which is added to house a formula to assist with producing the final results needed. I am still learning Smartsheet so maybe there is a different meaning in this community. In my case there is not a helper column, only three columns on Sheet 2 for the Comment, Course title, and person submitting the comment. On the source sheet, Sheet 1, there are multiple columns with the responses from the survey along with a date and time of submission column. Based on that information will these formulas work? If not, what modifications do I need to make? What column on which sheet would [Row#] reference?

Since Sheet 2 is currently being used and reviewed by others I erased the formula and used a link instead. I am going in and recreating the link each week to pull in new survey results until I could figure out the formula

Thank you for your time guiding me through this.

Paul

Paul Newcome

@Paul G. The helper columns in Smartsheet are the same as the helper columns inn Excel. There simply to house a formula that makes manipulation of data easier.

The [Row #] helper column in the "accepted answer" of this post goes on the destination sheet. The sheet where this information will be pulled TO (not from).

Paul G.

@Paul Newcome I placed the IFERROR formula in the "Comment" column on the sheet where this information will be pulled to. The comment pulls from sheet 1 and should appear in this column. Below is a screenshot of the formula and underneath is the unparseable error.

Paul Newcome

@Paul G.

There is a missing INDEX function as well as one too many closing parenthesis at the end of the formula.

=IFERROR(INDEX(.........), "")

Paul G.

@Paul Newcome

I apologies as I am obviously not understanding.

I have tried a number of variations of the formula and have not been able to write the formula correctly. I have left out IFERROR to see what the error is identified as.

=INDEX({SurveyCommentRng}, [Comment]1@row) produces #UNPARSEABLE

SurveyCommentRng is the named range for the column on sheet 1 where the survey comments appear. Comment is the header of the column where this formula appears on sheet 2.

Thank you for your patience as you walk me through this.

Paul