Only copy certain columns to a new sheet?

Pamela Wagner
Pamela Wagner ✭✭✭✭✭✭
inFormulas and Functions

I am trying to marry two sheets together, one is a new sheet for ticket intake and the other sheet has been in use by another team that has ticket information for one person but also additional information for their team. I don't want the team to have to enter in the data into the intake form in addition to their form. I know I can set up an automation to copy a row from the team sheet to the Intake sheet but the team sheet contains additional information that I don't need on the Intake sheet. Is there a way to set things up so that on the team sheet it's only copying a row with data from columns 1 - 5 and not 1 - 20?

· Share on Facebook Share on Twitter

Best Answers

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭
    edited 06/23/23 Answer ✓

    @Pamela WagnerThere's nothing in core Smartsheet.

    If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.

    Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message athttps://smartsheetguru.com/contact

    Darren Mullen "Smartsheet Guru", Member,Smart Consulting Services LLC

    Get my7 Smartsheet tips here

    Author of:Smartsheet User Guide for Accelerated Learning

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi@Pamela Wagner

    I hope you're well and safe!

    To add to Darren's excellent advice/answer.

    您可以使用cross-sheet公式结合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.

    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'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 weekend!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    ✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking 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

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Answers

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭
    edited 06/23/23 Answer ✓

    @Pamela WagnerThere's nothing in core Smartsheet.

    If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.

    Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message athttps://smartsheetguru.com/contact

    Darren Mullen "Smartsheet Guru", Member,Smart Consulting Services LLC

    Get my7 Smartsheet tips here

    Author of:Smartsheet User Guide for Accelerated Learning

    · Share on Facebook Share on Twitter
  • Pamela Wagner
    Pamela Wagner ✭✭✭✭✭✭

    Hmm, data mesh. I've not used that and will see what it can do for me. Thanks for now!

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi@Pamela Wagner

    I hope you're well and safe!

    To add to Darren's excellent advice/answer.

    您可以使用cross-sheet公式结合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.

    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'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 weekend!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    ✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking 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

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • Pamela Wagner
    Pamela Wagner ✭✭✭✭✭✭

    Hi@Andrée Staråhope you are well too! This certainly sounds like an approach as well! I do want to learn data mesh but this might be a good option too as I don't know if you can initiate a data mesh process if you don't have a license (I can set it up but another group has to administer it). Thanks much!

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Pamela Wagner

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
@maineL<\/a>,<\/p>

I haven't seen this possible without the use of a 3rd party integration. I don't believe it is supported natively in Smartsheet. The question comes up all the time though. <\/p>

BRgds,<\/p>

-Ray<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107090,"type":"question","name":"Calculate the # of months between 2 dates, with 1 date being static","excerpt":"Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12\/1\/22 as a static date in every calculation. So far I have been able to create a basic formula that works, however, I had to create…","snippet":"Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my…","categoryID":322,"dateInserted":"2023-06-29T15:55:21+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T17:24:43+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T17:21:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":158227,"lastUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T17:21:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3376114804,"url":"https:\/\/community.smartsheet.com\/discussion\/107090\/calculate-the-of-months-between-2-dates-with-1-date-being-static","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107090\/calculate-the-of-months-between-2-dates-with-1-date-being-static","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107090,"commentID":383163,"name":"Re: Calculate the # of months between 2 dates, with 1 date being static","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383163#Comment_383163","dateInserted":"2023-06-29T17:24:43+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T17:21:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T17:24:10+00:00","dateAnswered":"2023-06-29T17:11:25+00:00","acceptedAnswers":[{"commentID":383158,"body":"

Try a DATE function:<\/p>

=(NETDAYS([Requested Start Date]@row, DATE(2022, 12, 1)<\/strong>) \/ 365) * 12<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107083,"type":"question","name":"Excluding data when using COUNTIF","excerpt":"Hi, I have a graph on a dashboard based on the status of a project using the following formula for each status: =COUNTIF({Optimisation List Range 2}, \"Unallocated\") We do not want to pull through any project that is on hold but want to keep the status as it is on this column so have an additional column with the RAG Status…","snippet":"Hi, I have a graph on a dashboard based on the status of a project using the following formula for each status: =COUNTIF({Optimisation List Range 2}, \"Unallocated\") We do not want…","categoryID":322,"dateInserted":"2023-06-29T14:45:28+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T15:24:40+00:00","insertUserID":162412,"insertUser":{"userID":162412,"name":"JennyB.","url":"https:\/\/community.smartsheet.com\/profile\/JennyB.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T15:20:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162412,"lastUser":{"userID":162412,"name":"JennyB.","url":"https:\/\/community.smartsheet.com\/profile\/JennyB.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T15:20:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3376103408,"url":"https:\/\/community.smartsheet.com\/discussion\/107083\/excluding-data-when-using-countif","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107083\/excluding-data-when-using-countif","format":"Rich","lastPost":{"discussionID":107083,"commentID":383116,"name":"Re: Excluding data when using COUNTIF","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383116#Comment_383116","dateInserted":"2023-06-29T15:24:40+00:00","insertUserID":162412,"insertUser":{"userID":162412,"name":"JennyB.","url":"https:\/\/community.smartsheet.com\/profile\/JennyB.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T15:20:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T15:24:21+00:00","dateAnswered":"2023-06-29T15:16:19+00:00","acceptedAnswers":[{"commentID":383110,"body":"

@JennyB.<\/a> <\/p>

If I'm understanding your question correctly, you want to counts from one column but want to exclude projects that have an \"On Hold\" status in a second column. If so, then a countifs formula would work:<\/p>

=COUNTIFS({Column to Count}, \"Unallocated\", {Other column}, <>\"On Hold\")<\/p>

Hope this helps!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Automatic Update of Dropdown List?

    I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet? I can create a helper sheet if that would help. Any ideas? I don't believe I have the premium Smartsheets plan. Thank you! Lori

  • Calculate the # of months between 2 dates, with 1 date being static

    Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12/1/22 as a static date in every calculation. So far I have been able to create a basic formula that works, however, I had to create…

  • Excluding data when using COUNTIF

    Hi, I have a graph on a dashboard based on the status of a project using the following formula for each status: =COUNTIF({Optimisation List Range 2}, "Unallocated") We do not want to pull through any project that is on hold but want to keep the status as it is on this column so have an additional column with the RAG Status…