New Functions Feature Request

DW1
DW1 ✭✭✭✭
inFormulas and Functions

"ROW" - used to reference the sheet row number that can be referenced in other formulas. Something like '[email protected]'

'PREDECESSORS' = similar to current 'SUCCESSORS' function but returns the row references for predecessor tasks in dependency enabled project sheets.

'

Tags:
· Share on Facebook Share on Twitter

Comments

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@DW1

    Thanks for your input! If you have a moment, please post each of these as individual Idea Posts (perhaps with some examples) in theSmartsheet Product Feedback and Ideas topichere in the Community. This will allow other users to vote on your requests and add their own scenario to your suggestions.

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter
  • DW1
    DW1 ✭✭✭✭

    @Genevieve P.thank you, I was not sure where to add them.

    "ROW" - used to reference the sheet row number that can be referenced in other formulas. Something like '=ROW@row'.<\/p>

    Many use cases for this function, but as an example:<\/p>

    When using dependency enabled project sheets, the SUCCESSORS function will show the tasks that can start after the dates for a given predecessor task. I need to index\/match against the values returned by the SUCCESSORS function and report what dependent tasks can start if the predecessor task is marked complete, regardless of planned start\/finish dates.<\/p>","bodyRaw":"[{\"insert\":\"\\\"ROW\\\" - used to reference the sheet row number that can be referenced in other formulas. Something like '=ROW@row'.\\nMany use cases for this function, but as an example:\\nWhen using dependency enabled project sheets, the SUCCESSORS function will show the tasks that can start after the dates for a given predecessor task. I need to index\\\/match against the values returned by the SUCCESSORS function and report what dependent tasks can start if the predecessor task is marked complete, regardless of planned start\\\/finish dates.\\n\"}]","format":"rich","dateInserted":"2022-07-20T18:18:11+00:00","insertUser":{"userID":133011,"name":"DW1","title":"","url":"https:\/\/community.smartsheet.com\/profile\/DW1","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Ak9Yx9W5MHo!ZdHY2dmldXw!yZZu0KaUp7D","dateLastActive":"2022-09-08T12:31:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/93430\/new-function-row","embedType":"quote","name":"New Function - \"ROW\""}"> https://community.smartsheet.com/discussion/93430/new-function-row


    "PREDECESSORS" = similar to current "SUCCESSORS" function but returns the row references for predecessor tasks in dependency enabled project sheets. The values returned should be referenceable values in other formulas, e.g. omitting the SS\/FS\/FF task constraints in the dependency column so index\/match formulas can be applied.<\/p>","bodyRaw":"[{\"insert\":\"\\\"PREDECESSORS\\\" = similar to current \\\"SUCCESSORS\\\" function but returns the row references for predecessor tasks in dependency enabled project sheets. The values returned should be referenceable values in other formulas, e.g. omitting the SS\\\/FS\\\/FF task constraints in the dependency column so index\\\/match formulas can be applied. \\n\"}]","format":"rich","dateInserted":"2022-07-20T18:22:23+00:00","insertUser":{"userID":133011,"name":"DW1","title":"","url":"https:\/\/community.smartsheet.com\/profile\/DW1","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Ak9Yx9W5MHo!ZdHY2dmldXw!yZZu0KaUp7D","dateLastActive":"2022-09-08T12:31:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/93431\/new-function-predecessors","embedType":"quote","name":"New Function - \"PREDECESSORS\""}"> https://community.smartsheet.com/discussion/93431/new-function-predecessors


    · 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
@Ymandel<\/a> See if this version works:<\/p>

=COUNTIFS([Discharged Date]:[Discharged Date], >DATE(2023, 5, 26), [Date Activated]:[Date Activated], <=(DATE(2023, 6, 2)), [Status]:[Status], OR(@cell = \"Active\", @cell = \"Discharged\"))<\/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":107013,"type":"question","name":"#Incorrect Argument Set","excerpt":"Good Morning, I've been trying to figure this formula out but I can't seem to get it. I need to sum any amount in the computed count column, match it with the AP tech for a specific month. I've tried swapping the columns around but nothing seems to work. What am I missing? =SUMIFS({011-AP Archive AP Tech}, [Primary…","snippet":"Good Morning, I've been trying to figure this formula out but I can't seem to get it. I need to sum any amount in the computed count column, match it with the AP tech for a…","categoryID":322,"dateInserted":"2023-06-28T13:44:50+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T14:41:30+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-28T14:41:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156010,"lastUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-28T14:41:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":16,"score":null,"hot":3375923780,"url":"https:\/\/community.smartsheet.com\/discussion\/107013\/incorrect-argument-set","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107013\/incorrect-argument-set","format":"Rich","lastPost":{"discussionID":107013,"commentID":382850,"name":"Re: #Incorrect Argument Set","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382850#Comment_382850","dateInserted":"2023-06-28T14:41:30+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-28T14:41:41+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-28T14:41:39+00:00","dateAnswered":"2023-06-28T14:41:30+00:00","acceptedAnswers":[{"commentID":382850,"body":"

with a little more work - I got it!!<\/p>

=SUMIFS({011-AP Archive Computed Count}, {011-AP Archive AP Tech}, [Primary Column]118, {011-AP Archive Month}, 3)<\/p>

I swear I tried this earlier!! But I did confirm that it is adding up correctly!!<\/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":107009,"type":"question","name":"I need help with a if formula to get the $ amount total if another column = a specific item.","excerpt":"I want to make a formula in the Column \"A Total\" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.","snippet":"I want to make a formula in the Column \"A Total\" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.","categoryID":322,"dateInserted":"2023-06-28T12:55:48+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T13:46:52+00:00","insertUserID":162272,"insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-28T13:41:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162272,"lastUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-28T13:41:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3375918760,"url":"https:\/\/community.smartsheet.com\/discussion\/107009\/i-need-help-with-a-if-formula-to-get-the-amount-total-if-another-column-a-specific-item","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107009\/i-need-help-with-a-if-formula-to-get-the-amount-total-if-another-column-a-specific-item","format":"Rich","lastPost":{"discussionID":107009,"commentID":382831,"name":"Re: I need help with a if formula to get the $ amount total if another column = a specific item.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382831#Comment_382831","dateInserted":"2023-06-28T13:46:52+00:00","insertUserID":162272,"insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-28T13:41:35+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/N80WFJ95P6OH\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T13:45:50+00:00","dateAnswered":"2023-06-28T13:05:16+00:00","acceptedAnswers":[{"commentID":382818,"body":"

Hi @Elayne Smith<\/a>,<\/p>

The formula for this would be:<\/p>

=SUMIF(Project:Project, \"A\", [Grand Total]:[Grand Total])<\/p>

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

Hope this helps - any issues etc. then just post! 😊<\/span><\/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

  • Coiuntifs Formula Help Needed

    Hi! Can anyone please assist with the correct Formula? i'm trying to count how many rows are on a sheet(Reference Sheet) the criterea for the rows are as follows: =countifs STATUS Column is "Discharged" or "ACTIVE" , Discharged/Nonadmit Column is Greater than 05/19/2023 and Date activated column is less than 05/27/2023 I…

  • #Incorrect Argument Set

    Good Morning, I've been trying to figure this formula out but I can't seem to get it. I need to sum any amount in the computed count column, match it with the AP tech for a specific month. I've tried swapping the columns around but nothing seems to work. What am I missing? =SUMIFS({011-AP Archive AP Tech}, [Primary…

  • I need help with a if formula to get the $ amount total if another column = a specific item.

    I want to make a formula in the Column "A Total" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.