Analyse three columns content and produce a representation of request priority
Hi! TIA for any advice!
I have a Smartsheet fed by a user form, that provides me with bookings for a service provision.
As part of the service provision request in the service booking summary Smartsheet we have a department ID of the requester in one column, a "date created" column, and a column that shows the user/form created "service completion deadline" as a date, amongst other columns with inputs by the service requester.
We then have a currently manual input column of "priority", where if the service provision deadline is within 3 days of the request, the priority is "red" (using the circle RAG symbols).
If the service provision deadline is more than 3 days but less than 5, the priority is "yellow".
More than 5 days but less than 14 days "green".
More than 14 days, "blue".
I have a dashboard driven by column sum/calculations in the service booking Smartsheet that summarises various metrics such as requesting departments, hours estimated by the requester, actual hours spent on the request etc.
What I want to get is a report/dashboard widget/summary that will provide me with visibility of which departments are requesting with the most "red", "yellow" "green" "blue" priorities comparatively, so I can focus on those department requests and understand how to improve their requests to reduce the 'priority'/stress on the service provision, i.e. ask for the service provision more in advance.
Any suggestions very welcome, I've been thinking about it for a while, and can't settle on how best to accomplish this! I could just produce a count of priorities versus department labels, but I have 15 departments and the summaries of priorities become unmanageable/difficult to compare.
Help Article Resources
Check out the Formula Handbook template!