End of Year Rollover
Good afternoon,
I have been given the task to do a year end rollover. This has me duplicating each sheet, but then having to go in and change formulas for the additional sheets I'm creating. This is a very time consuming task, and I'm sure there has to be an easier way. After googling and trial and error, I have yet to figure it out. Does anyone have a better solution?
Thanks in advance!
Answers
-
Bassam Khalil ✭✭✭✭✭✭
Hi @Kris Guthier
Hope you are fine, did you try to save your original sheet which contain your formula as a template
☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
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":108361,"type":"question","name":"COUNTIFS+CONTAINS+AND","excerpt":"I am trying to write a sheet summary formula to count the number of rows where: Sprint Name contains \"Maintenance\", Date Resolved is not blank, and Bug or Request equals \"Bug\" [Sprint Name]:[Sprint Name], CONTAINS(\"Maintenance\") and [Date Resolved]:[Date Resolved], <>\"\" and [Bug or Request]:[Bug or Request], \"Bug\" I tried…","snippet":"I am trying to write a sheet summary formula to count the number of rows where: Sprint Name contains \"Maintenance\", Date Resolved is not blank, and Bug or Request equals \"Bug\"…","categoryID":322,"dateInserted":"2023-08-01T16:49:25+00:00","dateUpdated":null,"dateLastComment":"2023-08-01T17:13:01+00:00","insertUserID":139576,"insertUser":{"userID":139576,"name":"K Miller","url":"https:\/\/community.smartsheet.com\/profile\/K%20Miller","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hAtpoYJ45_M!Dl1TQ0DO0iQ!MPp8IQrC3KR","dateLastActive":"2023-08-01T17:51:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":139576,"lastUser":{"userID":139576,"name":"K Miller","url":"https:\/\/community.smartsheet.com\/profile\/K%20Miller","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hAtpoYJ45_M!Dl1TQ0DO0iQ!MPp8IQrC3KR","dateLastActive":"2023-08-01T17:51:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":35,"score":null,"hot":3381819746,"url":"https:\/\/community.smartsheet.com\/discussion\/108361\/countifs-contains-and","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108361\/countifs-contains-and","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108361,"commentID":388255,"name":"Re: COUNTIFS+CONTAINS+AND","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388255#Comment_388255","dateInserted":"2023-08-01T17:13:01+00:00","insertUserID":139576,"insertUser":{"userID":139576,"name":"K Miller","url":"https:\/\/community.smartsheet.com\/profile\/K%20Miller","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hAtpoYJ45_M!Dl1TQ0DO0iQ!MPp8IQrC3KR","dateLastActive":"2023-08-01T17:51:43+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-01T17:51:41+00:00","dateAnswered":"2023-08-01T17:08:09+00:00","acceptedAnswers":[{"commentID":388251,"body":"
CONTAINS() must include the range to compare. You can use @cell in this instance.<\/p>
=COUNTIFS([Sprint Name]:[Sprint Name], CONTAINS(\"Maintenance\", @cell), [Date Resolved]:[Date Resolved], <>\"\", [Bug or Request]:[Bug or Request], \"Request\")<\/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-01T20:06:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"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-01T20:06:15+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":[]}],"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":[]}">
