Referencing data in another sheet when other sheet is continually expanding in an IF(AND situation
Sorry if my question is too simple but this is an important bit of coding. I am referencing a continually expanding sheet what would be the coding like:
Quarter1计算的汇总表current sheet. Current Quarter1 is 04-2020.
I want to check if a value in a pad in a sheet (Well Pad List) matches a pad inspected in Pad Inspection Report Sheet.
=IF(AND(Combine Loc & Name1={Pad Inspection Report Sheet 7-7 2 [Combine Loc & Name]:[Combine Loc & Name]}, Quarter1= {Pad Inspection Report Sheet 7-7 2 [QTRfiscal]:[QTRfiscal}, 1,0)
Very Much Appreciated. Very important cell calculation.
Answers
-
Ramzi K ✭✭✭✭✭Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me:[email protected]
If this post helped you out, please help the Community bymarking it as the accepted answer/helpful.
-
Doyle54 ✭✭✭✭✭
Sure.
-
Doyle54 ✭✭✭✭✭
Yellow background is automatically calculated and is normally hidden
-
Doyle54 ✭✭✭✭✭
Correction
=IF(AND(Combine Loc & Name1={Pad Inspection Report Sheet 7-7 2 [CombPN&L]:[CombPN&L]}, Quarter1= {Pad Inspection Report Sheet 7-7 2 [QTRfiscal]:[QTRfiscal}, 1,0)
Help Article Resources
Categories
Check out theFormula Handbook template!
Try this:<\/p>
=AVG(COLLECT([Resolution Time (Days)]:[Resolution Time (Days)], [Date Closed]:[Date Closed], AND(@cell>DATE(2023, 6, 1), @cell<DATE(2023, 6, 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"}]},{"discussionID":108339,"type":"question","name":"Help on IF formula... validating two columns.","excerpt":"Hi, hope everyone is doing well? hoping someone could please help me with this formula, at the moment I am using the below formula to check the % from Score column and give the outcome result in the Result column and this is working fine, just wondering, if there a way I can include ‘Auto Fail’ in the above formula so if…","snippet":"Hi, hope everyone is doing well? hoping someone could please help me with this formula, at the moment I am using the below formula to check the % from Score column and give the…","categoryID":322,"dateInserted":"2023-08-01T11:55:34+00:00","dateUpdated":null,"dateLastComment":"2023-08-01T13:58:19+00:00","insertUserID":159253,"insertUser":{"userID":159253,"name":"ovelzone","url":"https:\/\/community.smartsheet.com\/profile\/ovelzone","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-01T16:27:58+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-01T15:39:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":23,"score":null,"hot":3381791633,"url":"https:\/\/community.smartsheet.com\/discussion\/108339\/help-on-if-formula-validating-two-columns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108339\/help-on-if-formula-validating-two-columns","format":"Rich","lastPost":{"discussionID":108339,"commentID":388190,"name":"Re: Help on IF formula... validating two columns.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388190#Comment_388190","dateInserted":"2023-08-01T13:58:19+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-01T15:39:04+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\/C0TNHS5393OE\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-01T13:41:25+00:00","dateAnswered":"2023-08-01T12:08:16+00:00","acceptedAnswers":[{"commentID":388167,"body":"
Try this one:<\/p>
=IF([Autofail]@row = \"Y\", \"Auto Fail\", IF([Score]@row < 0.5, \"Fail\", IF(AND([Score]@row > 0.5, [Score]@row < 0.65), \"Pass\", IF(AND([Score]@row > 0.64, [Score]@row < 0.75), \"Credit\", IF(AND([Score]@row > 0.74, Score]@row < 0.85), \"Distinction\", IF(AND([Score]@row > 0.84, [Score]@row <= 1), \"High Distinction\"))))))<\/p>"},{"commentID":388172,"body":"
This should work for you:<\/p>
=IF(Autofail@row = \"Y\", \"Auto Fail\", IF(Score@row< 0.5, \"Fail\", IF(Score@row< 0.65, \"Pass\", IF(Score@row< 0.75, \"Credit\", IF(Score@row<0.85, \"Distinction\", \"High Distinction\")))))<\/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":108324,"type":"question","name":"Help with Conditional Formatting","excerpt":"I have to create a report that has conditional formatting onto it. I am not really sure how to work Smartsheet's conditional formatting stuff. The example I was given to create was: \"Can color be added based on calendar logic (0-30 days since xxx, for example)\" There is an active sheet that we would use to put the…","snippet":"I have to create a report that has conditional formatting onto it. I am not really sure how to work Smartsheet's conditional formatting stuff. The example I was given to create…","categoryID":322,"dateInserted":"2023-07-31T20:42:59+00:00","dateUpdated":null,"dateLastComment":"2023-08-01T15:15:48+00:00","insertUserID":162512,"insertUser":{"userID":162512,"name":"Hannahsamd","title":"Hannah","url":"https:\/\/community.smartsheet.com\/profile\/Hannahsamd","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-01T15:16:52+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-01T15:39:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":34,"score":null,"hot":3381741527,"url":"https:\/\/community.smartsheet.com\/discussion\/108324\/help-with-conditional-formatting","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108324\/help-with-conditional-formatting","format":"Rich","lastPost":{"discussionID":108324,"commentID":388218,"name":"Re: Help with Conditional Formatting","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388218#Comment_388218","dateInserted":"2023-08-01T15:15:48+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-01T15:39:04+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\/Z4RH1R79HNAW\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-01T15:17:55+00:00","dateAnswered":"2023-08-01T06:13:07+00:00","acceptedAnswers":[{"commentID":388141,"body":"
Hi <\/strong>Hannahsamd,<\/strong><\/a><\/p> I have applied conditional formatting logic by referencing your requirement and you can use the attached image to create the same conditional formatting in Smartsheet.<\/p> If you are an admin or owner of the sheet, you can apply conditional formatting<\/p> By clicking this symbol of conditional formatting, then clicking in the Add New Rule you can give the condition then the format you want, and the specific column or the entire row as required, and then apply it.<\/p> <\/p> You can go through the below article of Conditional formatting too.<\/p>![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/BRP2I004JNZ9\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
![\"MicrosoftTeams-image](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/GUV5LYEGYBRD\/microsoftteams-image-2864-29.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
