Only copy certain columns to a new sheet?
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?
Best Answers
-
Darren Mullen ✭✭✭✭✭✭@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
-
Andrée Starå ✭✭✭✭✭✭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
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.
Answers
-
Darren Mullen ✭✭✭✭✭✭
@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
-
Pamela Wagner ✭✭✭✭✭✭
Hmm, data mesh. I've not used that and will see what it can do for me. Thanks for now!
-
Andrée Starå ✭✭✭✭✭✭
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
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.
-
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!
-
Andrée Starå ✭✭✭✭✭✭
SMARTSHEET EXPERT CONSULTANT & PARTNER
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.
Help Article Resources
Categories
=COUNTIFS([End Date]:[End Date], <= TODAY())<\/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":107098,"type":"question","name":"HAS and Children Functions","excerpt":"I have the following column1 formula: =IF(HAS(CHILDREN([Column2]@row), \"Text\"), 1, 0) which sets the value of column1 to 1 if \"Text\" is found in any children in Column2 This formula works well for a single nested file of children. However, I have a multi-level nested file (i.e. Parent1, Child1, Parent2, Child2) and it…","snippet":"I have the following column1 formula: =IF(HAS(CHILDREN([Column2]@row), \"Text\"), 1, 0) which sets the value of column1 to 1 if \"Text\" is found in any children in Column2 This…","categoryID":322,"dateInserted":"2023-06-29T18:01:34+00:00","dateUpdated":"2023-06-29T18:25:44+00:00","dateLastComment":"2023-06-29T19:48:55+00:00","insertUserID":158378,"insertUser":{"userID":158378,"name":"Mike Dufour","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Dufour","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DKvTQ7i6zPw!HGjTH6jjgiQ!wvw2rujGLYm","dateLastActive":"2023-06-30T01:51:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":158378,"lastUserID":158378,"lastUser":{"userID":158378,"name":"Mike Dufour","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Dufour","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DKvTQ7i6zPw!HGjTH6jjgiQ!wvw2rujGLYm","dateLastActive":"2023-06-30T01:51:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":44,"score":null,"hot":3376131029,"url":"https:\/\/community.smartsheet.com\/discussion\/107098\/has-and-children-functions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107098\/has-and-children-functions","format":"Rich","lastPost":{"discussionID":107098,"commentID":383208,"name":"Re: HAS and Children Functions","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383208#Comment_383208","dateInserted":"2023-06-29T19:48:55+00:00","insertUserID":158378,"insertUser":{"userID":158378,"name":"Mike Dufour","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Dufour","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DKvTQ7i6zPw!HGjTH6jjgiQ!wvw2rujGLYm","dateLastActive":"2023-06-30T01:51:55+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-29T19:50:03+00:00","dateAnswered":"2023-06-29T18:48:24+00:00","acceptedAnswers":[{"commentID":383194,"body":"
CHILDREN will only look at entries one level down. DESCENDANTS will look at all levels below current.<\/p>"},{"commentID":383208,"body":"
Thank you Carson, very helpful!<\/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":107085,"type":"question","name":"Using INDEX\/MATCH to pull a date and CountIF not recognizing as a date","excerpt":"Hi, I'm using INDEX\/MATCH to pull a date from one report into another based on the same ID number. If there is no match, an error message populates. =IFERROR(INDEX({Corporate CAPA Closed Effective Date}, MATCH([InfoCard Number]@row, {Corporate CAPA Closed InfoCard Number}, 0)), \"CAPA Not Closed\") Once the formula is…","snippet":"Hi, I'm using INDEX\/MATCH to pull a date from one report into another based on the same ID number. If there is no match, an error message populates. =IFERROR(INDEX({Corporate CAPA…","categoryID":322,"dateInserted":"2023-06-29T15:11:26+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T20:28:41+00:00","insertUserID":146974,"insertUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T20:28:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":146974,"lastUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T20:28:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3376123207,"url":"https:\/\/community.smartsheet.com\/discussion\/107085\/using-index-match-to-pull-a-date-and-countif-not-recognizing-as-a-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107085\/using-index-match-to-pull-a-date-and-countif-not-recognizing-as-a-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107085,"commentID":383212,"name":"Re: Using INDEX\/MATCH to pull a date and CountIF not recognizing as a date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383212#Comment_383212","dateInserted":"2023-06-29T20:28:41+00:00","insertUserID":146974,"insertUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T20:28:15+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-29T20:28:12+00:00","dateAnswered":"2023-06-29T17:10:07+00:00","acceptedAnswers":[{"commentID":383157,"body":"
The INDEX function should be retaining the data type (in this case date value). It sounds like the issue is that there is at least one blank or no-date value within the range. To accommodate that you would use:<\/p>
<\/p>
=COUNTIFS({All Sites CAPA}, CONTAINS(\"CAPA\", @cell), {All Sites CAPA Effective Date}, IFERROR(<\/strong>MONTH(@cell), 0)<\/strong> = 6, {All Sites CAPA Closed On Time?}, CONTAINS(\"CAPA Overdue\", @cell))<\/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"}]}],"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":[]}">
