I have been researching this topic for some time now and have been having trouble finding an answer - hoping the Smartsheet hivemind can help!
OBJECTIVE: To obtain a sum total of data submitted via a Smartsheet form, based on submission date.
CONTEXT: I work for a transportation company, and we use a Smartsheet form to record equipment moves from one location to another. For example, if a driver is moving Trailer 1 from Door A to Door B. These moves are reported on an ad-hoc basis, rather than "How many did you do today?", as it's easier to capture details on why the move(s) occurred.
COMPLICATION: Our customer requests the data in total moves per day in their own Smartsheet located on a separate Workspace. In my mind, this would've been an easy COUNTIFS solution, but the issue I'm running into is keeping the data flowing in real-time.
My current setup is:
- Trailer Moves Form where drivers enter the date, trailer type, and to/from locations.
- Trailer Moves Data sheet that collects the form submissions
- Trailer Moves Sum Total sheet that pulls the data from the above sheet into the format the customer wants.
Once the day rolls into the next, I can't figure out how to readjust the formulas on the Sum Total sheet to calculate for the new day, rather than the previous one.
Any advice would be much appreciated! Thank you everyone!
