I have a form individuals submit when they request tickets for an event. Our sheet manager can either approve or decline the requests. If approved, I have a formula set up that removes the amount of requested tickets from the total. The request entry is copied to a separate sheet that has a calendar of each event along with the amount of tickets available.
I'm running into an issue when requests are accepted and then later declined. The declined event is copied into my calendar sheet, but I can't figure out how to update my formula to remove the requested tickets. The formula I currently have is: =24 - SUMIF([Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Number of Tickets Needed]:[Number of Tickets Needed]). Below is a screenshot of the copied entries.
The last two entries show approved and then declined, however, my ticket formula only subtracted the 5 tickets from the 24 total, but when the declined row is copied, it doesn't update to reflect that the five tickets are now available again. How can I add a criteria to my ticket formula to re-add tickets to the total when a request is later declined? Or is there some other solution? Thank you!
=24-SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Approved") - SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Declined")
Yes! You would convert this to a SUMIFS and only account for requests that have been approved.
Hoping this works!
=24-SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Approved")
Thanks, Katy! Is there a way to add on to the formula to have it also then subtract the amount of tickets for when a separate line comes in showing declined?
=24-SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Approved" - [Approval Status]:[Approval Status], "Declined").
This is definitely not a correct formula but this is the last piece I'm trying to figure out how to squeeze in there.
=24-SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Approved") - SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Declined")
Try IF([payment voucher]@row=0,Sum([Parking Revenue Regular]@row:[Private boat parking revenue]@row),\"//www.santa-greenland.com/community/discussion/comment/\")<\/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":106883,"type":"question","name":"Needing some help with my current smartsheet project","excerpt":"So I'm coming across some issues with my workflows and functions with my current sheet, and I'm hoping somebody could help me out because I'm stumped. There are boxes I have set up on children rows that get checked manually to confirm a certain portion of the Main Task is complete. I'm currently in search of a way I can…","categoryID":322,"dateInserted":"2023-06-26T13:40:29+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T20:13:57+00:00","insertUserID":162756,"insertUser":{"userID":162756,"name":"SarahI","title":"Sarah","url":"https:\/\/community.smartsheet.com\/profile\/SarahI","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T19:19:04+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-06-26T20:54:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":43,"score":null,"hot":3375602066,"url":"https:\/\/community.smartsheet.com\/discussion\/106883\/needing-some-help-with-my-current-smartsheet-project","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106883\/needing-some-help-with-my-current-smartsheet-project","format":"Rich","lastPost":{"discussionID":106883,"commentID":382423,"name":"Re: Needing some help with my current smartsheet project","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382423#Comment_382423","dateInserted":"2023-06-26T20:13:57+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-26T20:54:28+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-26T15:51:35+00:00","dateAnswered":"2023-06-26T15:23:39+00:00","acceptedAnswers":[{"commentID":382304,"body":"
