I'm trying to create a system to take improvement ideas logged in a sheet through a triage process for each business unit and then finally into improvement initiatives that will address multiple ideas.
My idea is to have a sheet that sits behind, and the reports for each B/U for both the triage and initiative stages that have different columns based on the needs of that stage.
Capture: staff complete smartsheet form with idea title and business unit. These go into the sheet with auto number column for unique ID.
Triage stage: report for each B/U listing their ideas. Triage staff member from that B/U needs to assess the idea, decide if the idea will be queued, archived, or progress into either an existing or new initiative. Progressing initiatives need to automatically allocated a unique initiative ID.
Initiative stage: Report for each B/U. Initiatives need to be listed in a single line that capture all idea IDs that relate to that initiative. Each initiative is assigned an owner who needs to log deliverables, due date, and benefits.
Below images show how the stage flow and reports should work (cells colour-coded to show information flow from one stage to the next):
Capture sheet: prior to any triaging, with all columns needed for the following reports:
Finance Triage report: where a staff member selects an action and allocates progressing ideas an "Initiative" (the yellow cells). The Initiative ID unique identifier needs to be auto allocated based on the Initiative typed into the Initiative column, so "Reports" initiative with 2 entries are both "Fin-01" and "KPIs" initiative is "Fin-02":
Finance Initiatives report: where only unique initiatives are listed, and the related Idea IDs are all auto captured in the orange "Idea IDs" column. The owners (Bob and Betty) access this report to add deliverables, due date, and benefits:
I can build the intake sheet and reports, the challenges I'm having are:
- Getting a unique "Initiative ID" auto-allocated for unique initiative titles once a title is added to the "Initiative" column, and ensuring it assigns the same Initiative ID if that title already exists, or a new ID if the title doesn't exist.
- Auto-populating (through a formula) the "Idea IDs" column with the IDs of the ideas associated with that initiative.
- Getting the 3rd Initiatives report to list the initiatives in a single line.
Any help would be greatly appreciated!
James
