Copy rows to another sheet using workflow

Hello

I have a workflow which triggers when a column (calculation) changes to value to anything above 15.

The workflow then is set to move these rows to another worksheet, using a condition whereby status = 'open' from another column.

All looks good, but nothing happens on the destination sheet when I run it.

What am I missing?

Katie

image.png


Answers

  • KatieDi
    KatieDi ✭✭

    Ok, so I've got it working, but how can I work it in reverse?

    Scenario:

    Project risks that score 15+ are copied from the Project risk log (source sheet) to the Programme risk log (destination sheet).

    But, I need them to be removed from the destination sheet if the score drops down below 15.

    I can push them up, but changing them on the source sheet doesn't then change them on destination sheet - it stays what it was when it was copied.

    Any ideas?

    Katie

  • KatieDi
    KatieDi ✭✭

    More info.. If I increase the score, e.g. from 16 to 25, it adds the row again.

    I think, quite simply (!) is what I'm trying to do is have a dynamic row in the destination sheet that updates and behaves the same as the source sheet, but the condition is that only displays in the destination sheet if it scores over 15.

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭

    Hi@KatieDi

    I would try to use a report for this if possible. You could set the report to only show rows where the value is between the numbers you need. And if you need more columns on the report that aren't needed on the source sheet, you could add them to the source sheet and hide them. Or...maybe have the source sheet be a file that no one accesses and have the source sheet people work off of be a report as well with filters opposite of the other report.

    I know its the answer you were looking for but I hope that helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet PLATINUM Partner

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@KatieDi

    I hope you're well and safe!

    If I understand your process correctly. Can you use something as a unique identifier for each row?

    您可以使用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.

    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.

    Make sense?

    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 for help with Smartsheet, integrations, general workflow advice, or anything else.

  • KatieDi
    KatieDi ✭✭

    Hi Andree,

    Thank you for your response. I think it may work, however, I'd have no idea where to start with a VLOOKUP. I've never done it before and I don't know how/where I'd use it.

    Each row already has a unique identifier (the Risk ID) so could use that - I just don't know how I'd go about getting that row into the destination sheet when it meets certain criteria.

    Katie

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi<\/p>

I believe this formula, entered in the TITLE (Formula) column would work for you:<\/p>

=IF(ISBLANK([INT \/ DOM \/ TEMP \/ NG]@row), \"//www.santa-greenland.com/community/discussion/107129/\", TITLE$1)<\/p>


<\/p>

It creates this:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>

You start with an IF<\/p>

For the logical expression you use ISBLANK<\/p>

ISBLANK([INT \/ DOM \/ TEMP \/ NG]@row) is checking that the cell in the column INT \/ DOM \/ TEMP \/ NG is blank<\/p>

If that is true it puts in \"//www.santa-greenland.com/community/discussion/107129/\" (ie nothing)<\/p>

If it is false it puts in the value in the TITLE column, but instead of TITLE@row, it pulls just from row 1 because of the $1<\/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":112287,"type":"question","name":"Count two columns of a sheet that each match specific criteria","excerpt":"Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the Status is \"Approved\" in another column.","snippet":"Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the…","categoryID":322,"dateInserted":"2023-10-27T18:08:34+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T20:42:18+00:00","insertUserID":169195,"insertUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169195,"lastUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3396870652,"url":"https:\/\/community.smartsheet.com\/discussion\/112287\/count-two-columns-of-a-sheet-that-each-match-specific-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112287\/count-two-columns-of-a-sheet-that-each-match-specific-criteria","format":"Rich","lastPost":{"discussionID":112287,"commentID":402120,"name":"Re: Count two columns of a sheet that each match specific criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402120#Comment_402120","dateInserted":"2023-10-27T20:42:18+00:00","insertUserID":169195,"insertUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T21:43:37+00:00","dateAnswered":"2023-10-27T20:13:32+00:00","acceptedAnswers":[{"commentID":402113,"body":"

@Bob Schweighart<\/a> you just need the COUNTIFS formula and put those 2 criterion in it. Are they distinct counts for the 4 different projects? If so, that is easier. If you are counting all 4, then you need to decide on an imbedded OR or a NOT.<\/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":112242,"type":"question","name":"Can I use Index\/Match and Left functions in one formula?","excerpt":"Hello, can someone help me with the left function within the index\/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job Number]@row,5), {Master Job #}, 0)) I also tried creating a helper column, Job Prefix, using =LEFT([Job Number]@row, 5) and using that result in the SO…","snippet":"Hello, can someone help me with the left function within the index\/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job…","categoryID":322,"dateInserted":"2023-10-26T22:39:07+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T17:46:03+00:00","insertUserID":157316,"insertUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":157316,"lastUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":33,"score":null,"hot":3396791110,"url":"https:\/\/community.smartsheet.com\/discussion\/112242\/can-i-use-index-match-and-left-functions-in-one-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112242\/can-i-use-index-match-and-left-functions-in-one-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112242,"commentID":402089,"name":"Re: Can I use Index\/Match and Left functions in one formula?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402089#Comment_402089","dateInserted":"2023-10-27T17:46:03+00:00","insertUserID":157316,"insertUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T17:46:07+00:00","dateAnswered":"2023-10-27T17:30:20+00:00","acceptedAnswers":[{"commentID":402081,"body":"

There we go. The [SO] column looks like it is being manually populated which is storing the data as numerical data, but the LEFT function outputs text.<\/p>


<\/p>

Insert a column on the reference sheet and use<\/p>

=SO@row + \"//www.santa-greenland.com/community/discussion/107129/\"<\/p>

plus quote quote<\/p>


<\/p>

This will convert all entries into a text string so that data types match. You would then match on this helper column in your INDEX\/MATCH.<\/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":[]}">

Trending in Formulas and Functions