what is the suitable function

Ibrahim Khalaf
Ibrahim Khalaf ✭✭
inFormulas and Functions

I have 2 sheets, the first one is a table that shows the following

image.png

and I have another sheet all what i need when i write the values in the first and the second column the data in the third columns appear

what suitable function could be used

· Share on Facebook Share on Twitter

Best Answer

Answers

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公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Try this:<\/p>

=IF([Date Response Sent]@row <> \"\", [Date Response Sent]@row + 30, IF([Response Due Date]@row <> \"\", [Response Due Date]@row, DATEONLY([Date Created]@row) + 30))<\/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"},{"tagID":485,"urlcode":"community-champions","name":"Community Champions"}]},{"discussionID":108695,"type":"question","name":"RYG Health Status based on \"Activity Status\" and Sprint End Date","excerpt":"I'm a fairly new Smartsheet User but starting to catch on fast. Please forgive the noobie question. I'm trying to set a Red, Yellow, Green - Health Status to an activity but I would like it to be based on the following parameters: 1) Red IF Today(-3)days from the [Sprint End Date] AND [SPRINT ACTIVITY STATUS] = \"In…","snippet":"I'm a fairly new Smartsheet User but starting to catch on fast. Please forgive the noobie question. I'm trying to set a Red, Yellow, Green - Health Status to an activity but I…","categoryID":322,"dateInserted":"2023-08-08T21:59:50+00:00","dateUpdated":null,"dateLastComment":"2023-08-09T21:30:35+00:00","insertUserID":164799,"insertUser":{"userID":164799,"name":"JacksonElla","title":"Senior PM","url":"https:\/\/community.smartsheet.com\/profile\/JacksonElla","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-09T21:26:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-09T21:50:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":44,"score":null,"hot":3383153425,"url":"https:\/\/community.smartsheet.com\/discussion\/108695\/ryg-health-status-based-on-activity-status-and-sprint-end-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108695\/ryg-health-status-based-on-activity-status-and-sprint-end-date","format":"Rich","lastPost":{"discussionID":108695,"commentID":389917,"name":"Re: RYG Health Status based on \"Activity Status\" and Sprint End Date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389917#Comment_389917","dateInserted":"2023-08-09T21:30:35+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-09T21:50:00+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-08-09T14:55:08+00:00","dateAnswered":"2023-08-09T14:26:11+00:00","acceptedAnswers":[{"commentID":389791,"body":"

Internet is back up for a minute!<\/p>


<\/p>

Ok. In that case here is what we are working with:<\/p>

If the Status is \"Complete\" then green<\/p>

If the Status is not complete and the [Sprint End Date] is in the past then red<\/p>

If the Status is \"In Progress\" or \"In QA\" and the [Sprint End Date] is in the next three days then red<\/p>

If the Status is \"In Progress\" or \"In QA\" or \"On Hold\" and the [Sprint End Date] is in the next 6 days then yellow<\/p>


<\/p>

After the formula, I will point out a few potential scenarios that would not output any color based on the above logic. I will also show the difference between trying to follow the same syntax patterns evaluating status and date vs nesting in a specific order.<\/p>


<\/p>

Here is the formula I would use for the above criteria:<\/p>

=IF(Status@row = \"Complete\", \"Green\", IF(OR(AND(OR(Status@row = \"In Progress\", Status@row = \"In QA\"), [Sprint End Date]<= TODAY(3)), [Sprint End Date]@row< TODAY()), \"Red\", IF(AND(OR(Status@row = \"In Progress\", Status@row = \"In QA\", Status@row = \"On Hold\"), [Sprint End Date]@row<= TODAY(6)), \"Yellow\")))<\/p>


<\/p>


<\/p>

Here is a more basic example of the differences between using extra functions for complete and past due vs using nesting logic.<\/p>

=IF(AND(Status@row <> \"Complete\", [Sprint End Date]@row< TODAY()), \"Red\", IF(Status@row = \"Complete\", \"Green\", ...................<\/p>


<\/p>

vs leveraging nesting logic<\/p>

IF(Status@row = \"Complete\", \"Green\", IF([Sprint End Date]@row< TODAY(), \"Red\", ...................<\/p>


<\/p>

You can see that knowing our criteria and leveraging nesting logic makes an impact on both syntax as well as efficiency (and can save some keystrokes too).<\/p>


<\/p>

It also looks like if the status is anything other than one of the ones specifically mentioned, it will show as blank (until it becomes past due). And any of the statuses mentioned will be blank if the end date is 7 or more days in the future.<\/p>

That's not to say you HAVE to change anything. If it works for you then it works for you, but I just wanted to point out that there may be some unexpected gaps in the logic if we haven't thought about what we want to see for each scenario (or at least the scenarios that we want to see some kind of indicator for).<\/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":108708,"type":"question","name":"need pls. advise for formula","excerpt":"Hi, pls. see pic. below: I'm looking formula that will light in green column called greenlight to install , when all other columns are checked and red when at least 1 column is unchecked Thanks in advance Yuval","snippet":"Hi, pls. see pic. below: I'm looking formula that will light in green column called greenlight to install , when all other columns are checked and red when at least 1 column is…","categoryID":322,"dateInserted":"2023-08-09T05:06:59+00:00","dateUpdated":null,"dateLastComment":"2023-08-09T07:38:30+00:00","insertUserID":164719,"insertUser":{"userID":164719,"name":"Yuval","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Yuval","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtdS5R8g61Mz8YpTO-F33O2rTr6kCdYdluWCAoRiw3tuqjo=s96-c","dateLastActive":"2023-08-09T19:02:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164719,"lastUser":{"userID":164719,"name":"Yuval","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Yuval","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtdS5R8g61Mz8YpTO-F33O2rTr6kCdYdluWCAoRiw3tuqjo=s96-c","dateLastActive":"2023-08-09T19:02:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":41,"score":null,"hot":3383125529,"url":"https:\/\/community.smartsheet.com\/discussion\/108708\/need-pls-advise-for-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108708\/need-pls-advise-for-formula","format":"Rich","lastPost":{"discussionID":108708,"commentID":389689,"name":"Re: need pls. advise for formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389689#Comment_389689","dateInserted":"2023-08-09T07:38:30+00:00","insertUserID":164719,"insertUser":{"userID":164719,"name":"Yuval","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Yuval","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtdS5R8g61Mz8YpTO-F33O2rTr6kCdYdluWCAoRiw3tuqjo=s96-c","dateLastActive":"2023-08-09T19:02:55+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\/4XRBHBBDEXBW\/green.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"green.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-09T07:50:48+00:00","dateAnswered":"2023-08-09T07:27:46+00:00","acceptedAnswers":[{"commentID":389688,"body":"

Hi Yuval,<\/strong> <\/p>

You can try this formula for your condition- <\/p>

=IF(AND([Materials in Rigotec\/Talad]@row = true, [Materials in CR]@row = true, [HC in Place]@row = true, [QA\/QC done before install]@row = true), \"Green\", IF(AND([Materials in Rigotec\/Talad]@row = false, [Materials in CR]@row = false, [HC in Place]@row = false, [QA\/QC done before install]@row = false), \"\", IF(OR([Materials in Rigotec\/Talad]@row = false, [Materials in CR]@row = false, [HC in Place]@row = false, [QA\/QC done before install]@row = false), \"Red\"))) <\/p>

Hope this will work for you <\/p>"},{"commentID":389689,"body":"

Cool, many thanks Kaveri, working as watch, just copy and paste, perfect<\/p>

Yuval<\/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

  • Due Date w/Multiple Conditions

    Hi Community! I'm looking for some help. I've been tasked with creating a "notice tracker". The team would like the [Due Date]@row to be some what of a dynamic date. Here is a snip of the sheet and the columns I'll be using. The sheet is fed via a form. The form has "Response Due Date" but is not required. It will only be…

  • RYG Health Status based on "Activity Status" and Sprint End Date

    I'm a fairly new Smartsheet User but starting to catch on fast. Please forgive the noobie question. I'm trying to set a Red, Yellow, Green - Health Status to an activity but I would like it to be based on the following parameters: 1) Red IF Today(-3)days from the [Sprint End Date] AND [SPRINT ACTIVITY STATUS] = "In…

  • need pls. advise for formula

    Hi, pls. see pic. below: I'm looking formula that will light in green column called greenlight to install , when all other columns are checked and red when at least 1 column is unchecked Thanks in advance Yuval