Formula to assign a contact on all rows of main parent cell

GabyC
GabyC ✭
inFormulas and Functions

Hello!

I want to assign all rows under the main parent cell (has multiple sub parent cells within) the contact that we inputted into the main parent row. When tasks are added within the project, I want it to auto assign that row. Can anyone help me with a formula for this?


Thanks!

· Share on Facebook Share on Twitter

Answers

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

    Hi@Gaby Colvis

    I hope you're well and safe!

    Have you explored using a Workflow with the Assign People Action?

    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 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
  • Kelly Drake
    Kelly Drake Overachievers Alumni

    I would recommend putting this into two columns so that you can leverage a column formula to autopopulate. Otherwise, you'd have to copy/paste the formula into the children rows which would be about the same amount of work as just entering in the names into the field.

    Or could you use the Assign a Person automation if you had a column that could be leveraged as a condition in the workflow. (This could require one workflow per person/contact though so this could be way more work to build out than necessary.)


    If you leverage 2 columns for this - for the purpose of the formula, I'll use Lead and Project Task as the column for the task names. Also, I would assume that Lead is blank in the the children rows and then put this formula in the Assigned To column as a column formula.

    =IF(COUNT(ANCESTORS([Project Task]@row)) = 0,[email protected], IF(COUNT(ANCESTORS([Project Task]@row)) > 0, INDEX(ANCESTORS([email protected]), 1), "tbd"))

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| senior systems analyst | global growth & concepts

    · 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公式手册模板!
Hi @Clare123<\/a> <\/p>

I hope you're well and safe!<\/p>

Yes, absolutely.<\/p>

Here's the structure.<\/p>

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))<\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":106314,"type":"question","name":"Flexible Duration value when calculating scheduling dates","excerpt":"I have a Smartsheet which is a project plan with the Dependencies and Duration columns is being used in scheduling calculations to automatically deriving the start\/end dates for each task. However, I need the Duration to be dynamic e.g. linking cell to a cell in another sheet or to contain a formulae.. However, I cannot…","categoryID":322,"dateInserted":"2023-06-12T12:39:46+00:00","dateUpdated":null,"dateLastComment":"2023-06-13T11:22:45+00:00","insertUserID":162290,"insertUser":{"userID":162290,"name":"Mark Duddy","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Mark%20Duddy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-13T11:41:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-13T15:38:41+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":56,"score":null,"hot":3373230151,"url":"https:\/\/community.smartsheet.com\/discussion\/106314\/flexible-duration-value-when-calculating-scheduling-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106314\/flexible-duration-value-when-calculating-scheduling-dates","format":"Rich","lastPost":{"discussionID":106314,"commentID":380264,"name":"Re: Flexible Duration value when calculating scheduling dates","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380264#Comment_380264","dateInserted":"2023-06-13T11:22:45+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-13T15:38:41+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"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-13T11:24:19+00:00","dateAnswered":"2023-06-13T11:22:45+00:00","acceptedAnswers":[{"commentID":380264,"body":"

Hi @Mark Duddy<\/a> <\/p>

If you're using dependencies in a Project Sheet, then the Duration column is not able to be updated via formulas <\/a>or cell links. This is because it's used in combination with the Start and Finish dates in your sheet to automatically generate data (e.g. if you add the Start date and a Duration, the Finish date populates. If you add the Start date and Finish date, the Duration automatically populates).<\/p>

You could set up your sheet to use regular columns and use your own formulas to generate automatic dates based on a text\/number \"duration\" column, but then these details would not be associated with Critical paths or other Project features in a Gantt chart.<\/p>

Cheers,<\/p>

Genevieve<\/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":106342,"type":"question","name":"I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula","excerpt":"I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula. Example of 2 IF formulas: =IF([Member 2 Registration Type]@row = \"Golf & Dinner - General Public\", \"135\") and =IF([Member 2 Registration Type]@row = \"Dinner Only\", \"60\") The IF formulas are working as they should and are…","categoryID":322,"dateInserted":"2023-06-12T21:19:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-13T02:23:40+00:00","insertUserID":162311,"insertUser":{"userID":162311,"name":"Jhinkle","url":"https:\/\/community.smartsheet.com\/profile\/Jhinkle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!209A_9K4WHo!nZ9On1SJEvo!KaPQ5aRebqf","dateLastActive":"2023-06-13T02:23:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162311,"lastUser":{"userID":162311,"name":"Jhinkle","url":"https:\/\/community.smartsheet.com\/profile\/Jhinkle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!209A_9K4WHo!nZ9On1SJEvo!KaPQ5aRebqf","dateLastActive":"2023-06-13T02:23:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3373228985,"url":"https:\/\/community.smartsheet.com\/discussion\/106342\/i-am-trying-to-use-sum-to-add-the-values-from-multiple-cells-in-that-were-populated-by-an-if-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106342\/i-am-trying-to-use-sum-to-add-the-values-from-multiple-cells-in-that-were-populated-by-an-if-formula","format":"Rich","lastPost":{"discussionID":106342,"commentID":380235,"name":"Re: I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380235#Comment_380235","dateInserted":"2023-06-13T02:23:40+00:00","insertUserID":162311,"insertUser":{"userID":162311,"name":"Jhinkle","url":"https:\/\/community.smartsheet.com\/profile\/Jhinkle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!209A_9K4WHo!nZ9On1SJEvo!KaPQ5aRebqf","dateLastActive":"2023-06-13T02:23:49+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-13T02:23:47+00:00","dateAnswered":"2023-06-13T02:23:40+00:00","acceptedAnswers":[{"commentID":380235,"body":"

Thanks! That worked!<\/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":9978,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions