cvc.lukes ✭✭
06/25/21 in Formulas and Functions
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.
Tags:
- Formulas
- Project Management
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
06/25/21 Answer ✓
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), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
2 · Share on FacebookShare on Twitter
Answers
Andrée Starå ✭✭✭✭✭✭
06/25/21
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!
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.
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
06/25/21
Have you looked into using a report?
See AlsoCOUNTIFS function with multiple criteriaCOUNTIFS function with multiple criteriaCustom Text for SymbolsHow to pull data from one sheet to another easilyYou 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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
0 · Share on FacebookShare on Twitter
cvc.lukes ✭✭
06/25/21
@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.
0 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
06/25/21
@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!
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.
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
06/25/21 Answer ✓
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), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
2 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
06/25/21
@Paul Newcome
Excellent tip! 👍️
I'm so used to INDEX/MATCH that I didn't think of not using MATCH.
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.
0 · Share on FacebookShare on Twitter
cvc.lukes ✭✭
06/25/21
Thanks @Paul Newcome this will work!
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
06/25/21
@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
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
0 · Share on FacebookShare on Twitter
Paul G. ✭✭✭✭✭
12/06/22
@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
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
12/06/22
@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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
0 · Share on FacebookShare on Twitter
Paul G. ✭✭✭✭✭
12/08/22
@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
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
12/08/22
@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).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
0 · Share on FacebookShare on Twitter
Paul G. ✭✭✭✭✭
12/08/22
@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.
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
12/09/22
@Paul G.
There is a missing INDEX function as well as one too many closing parenthesis at the end of the formula.
=IFERROR(INDEX(.........), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
0 · Share on FacebookShare on Twitter
Paul G. ✭✭✭✭✭
12/13/22
@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
0 · Share on FacebookShare on Twitter
«12»
Help Article Resources
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.7K Get Help
- 376 Global Discussions
- 205 Industry Talk
- 437 Announcements
- 15 Community Corner Newsletter
- 139 Brandfolder
- 129 Just for fun
- 129 Community Job Board
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!