Analyse three columns content and produce a representation of request priority

JohnaP

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.

JamesB

@JohnaP

I have something similar to track service requests. I would suggest you create a sheet with the following columns.

Department, Red, Yellow, Green, Blue.

In the Department column, manually put in the 15 department names, in the other columns, use a countif formula to get the total number of each color, you can then bring those into Metrics and Graphs on your dashboard.

JamesB

@JohnaP

I have something similar to track service requests. I would suggest you create a sheet with the following columns.

Department, Red, Yellow, Green, Blue.

In the Department column, manually put in the 15 department names, in the other columns, use a countif formula to get the total number of each color, you can then bring those into Metrics and Graphs on your dashboard.

JohnaP

Great idea, thanks! Can't believe I struggled with that for so long! Cheers!