SmartSheets as Project Tracker-Too many manual steps. There has to be a better way

thewell

Buckle up. This one's a longread.

TLDR: Skip to steps 5, 8 and 9 on this list.

I've been using SmartSheets over the past month or so to replace my project management / status trackers. I like the automations (mostly) and that it seems fairly easy to use. It seems like it has some problems that I find very surprising and make me wonder if I just don't know of a better way.

Here's what I do:

1. I build a "Project Tracker" grid for each client engagement. The primary column is the phase of the engagement-Planning, Testing, Evaluation, etc. Tasks are in the next column to the right and are nested within their respective phase.
2. Beside that, I have a column for assignees, statuses (drop down list), and due dates. Way to the right, I have more columns: one for each status option (not started, on hold, in progress, etc.) and a countif formula that references the full range of cells in the status column for each phase (e.g. several columns to the right where the word "planning" is in my Primary column is this formula: =COUNTIF($Status9:$Status22, "On hold") that gives me a count of all tasks nested within the Planning Phase (rows 9-22) that show a status of "on hold").
3. I have another grid named Metrics. Inside the Metrics grid, the Primary Column lists all 6 of my statuses: Not started, on hold, in progress, ready for review, review notes, and complete.
4. The columns in the Metrics grid are labeled for each of the client engagement phases. So, a column for pre-planning, a column for planning, a column for testing, so on. It ends up being a minimum of 7 columns, but can run up to 16 or more if I also want to track sub-phases (which I often do).
5. HERE IS THE PROBLEM: I want the Metrics Grid to populate with the COUNTS pulled from the Project Tracker grid. I have to click on the cell in Metrics for the Status / Phase, click "Cell Linking", navigate to the correct cell on the dialogue window that appears, and click OK. And I have to do this for EVERY SINGLE CELL. 6 statuses X 7 phases = A minimum of 42 INDIVIDUAL CELLS and 126 mouse clicks. I'm dead serious when I say that I have developed wrist pain this week from building these task trackers. I have to be doing something wrong here.
6. Dragging over the whole range of cells doesn't work because of the difference in how the Metrics grid is laid out. In the Project Tracker, project phases are in their own row vertically. In the Metrics grid, the project phases are in columns going horizontally. So just dragging the cells means that the data would be incorrect.
7. The Project Tracker is set up the way that it is because that's what's easiest for my team to read and work with. The Metrics grid is set up the way that it is because of the way the Dashboards work-I want to use the Pie / Donut graphs to show, for a given phase, what percentage of tasks are On Hold, what percentage are Complete, etc. And the only way to show it seems to be to lay it out in that way with each status on its own row and the phases as their own columns.
8. While we're on the subject of dashboards and the Donut graphs: Why, in an application named "SmartSheets," am I having to manually go in to every single one of my donut graphs each time to fix the color schemes? Why can't I set the color scheme once and have it apply to any new donut graph that I create for the dashboard? On one donut graph, % complete shows up as red. On another, it shows up as green. And I have to go in and manually set each one to the correct color.
9. Last one that's a minor annoyance: The automations pop up. When I make a new grid and set a column type to "Contact List" and add someone to a cell in that column, I get this annoying pop up that says "Because you assigned a row in this sheet, you can now notify team members when a row gets assigned to them. -Not Right Now and Enable." I've searched and searched and searched and cannot find away to disable this irritating pop up. How do I turn it off so that it will stop disrupting my work with information that I already know and do not need?

Greatly appreciate any assistance that the community can provide. Thanks!

KPH

Hi @thewell

Very quick thought on 5-7.

With the way your sheet is set up can you set up cross sheet formula, and add some sort of ID column and then do something clever with INDEX and MATCH to pull in what you need?