\n https:\/\/community.smartsheet.com\/discussion\/comment\/380093#Comment_380093\n <\/a>\n<\/div>\nYou would need to add another column to be able to make that calculation.<\/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":106279,"type":"question","name":"How do I create and If Formula pulling from another sheet to designate indicators?","excerpt":"Good Morning All, I am working on a Overall Health for a Project that looks like this: I need to update the lights by using an if statement looking at three different columns of another sheet that are not next to one another on the sheet, example of that sheet below In the first sheet for each Column I need to look at the…","categoryID":322,"dateInserted":"2023-06-09T16:40:18+00:00","dateUpdated":"2023-06-09T16:47:15+00:00","dateLastComment":"2023-06-12T18:25:42+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T18:37:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-06-12T18:23:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":80,"score":null,"hot":3372929760,"url":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","format":"Rich","lastPost":{"discussionID":106279,"commentID":380161,"name":"Re: How do I create and If Formula pulling from another sheet to designate indicators?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380161#Comment_380161","dateInserted":"2023-06-12T18:25:42+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-06-12T18:23:21+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\/XSK1SHR157VN\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-11T16:45:30+00:00","dateAnswered":"2023-06-11T12:13:51+00:00","acceptedAnswers":[{"commentID":380000,"body":"
If it isn't weekly, then you would use a COUNTIFS in nested IFs.<\/p>
=IF(COUNTIFS({Criteria}, @cell = \"Infrastructure\", {Due Date}, @cell<= TODAY())> 0, \"Red\", IF(.....<\/p>
<\/p>
You would continue that out and adjust the number of days in the TODAY() function to determine your RYG.<\/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":106303,"type":"question","name":"Stumped on a formula for a parent cell to aggregate names of the subtasks.","excerpt":"I'd like to capture the Individual Project names, tracked by subtasks, and aggregate them in the Parent Cell (All Project Names). This way, when minimized, I don't have to expand to see which Sites the task is for. Also for Dashboard views, to see which Sites are part of the parent project that is being tracked. Screenshot…","categoryID":322,"dateInserted":"2023-06-10T16:49:08+00:00","dateUpdated":null,"dateLastComment":"2023-06-10T21:44:52+00:00","insertUserID":162134,"insertUser":{"userID":162134,"name":"CamSME","title":"Process Developer","url":"https:\/\/community.smartsheet.com\/profile\/CamSME","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-11T14:32:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":116407,"lastUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9WIC1TB7AJQT\/nP1GU5H4ITA1P.png","dateLastActive":"2023-06-12T16:35:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":36,"score":null,"hot":3372851040,"url":"https:\/\/community.smartsheet.com\/discussion\/106303\/stumped-on-a-formula-for-a-parent-cell-to-aggregate-names-of-the-subtasks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106303\/stumped-on-a-formula-for-a-parent-cell-to-aggregate-names-of-the-subtasks","format":"Rich","lastPost":{"discussionID":106303,"commentID":379990,"name":"Re: Stumped on a formula for a parent cell to aggregate names of the subtasks.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379990#Comment_379990","dateInserted":"2023-06-10T21:44:52+00:00","insertUserID":116407,"insertUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9WIC1TB7AJQT\/nP1GU5H4ITA1P.png","dateLastActive":"2023-06-12T16:35: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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/OO7GA4QG90VK\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-10T17:50:36+00:00","dateAnswered":"2023-06-10T17:37:08+00:00","acceptedAnswers":[{"commentID":379986,"body":"
@CamSME<\/a> you'll use join and collect with children or decedents as the range. This video might help.<\/p>