Formula Assistance

BKindred6

I'm trying to create a automated system in Smartsheet's that takes criteria from a form submission (Unit, Start Date, End Date, Day of the Week and Shift time) to cross check another Smartsheet that has the full calendar year by week and if all criteria from the submission match on the calendar sheet for the particular(Unit, week range, day, and shift time) will subtract from the max available spots using the student count entered in the submission. Is there a formula that can do this? I've tried using a countif formula of all criteria with no success.

Ryan Kramer

BKindred6,

I haven't seen anything yet that does stuff this complex but you could complete this task with a small amount of programming and utilizing Smartsheet's APIs.

Basically you could have the script run once a day and go through each entry, calculate the totals by the 5 criteria points and then aggregate and populate into your final sheet or whatever you are using for keeping track of open spots/closed spots.

Ryan