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?

Andrée Starå

Hi @thewell

I hope you're well and safe!

To add to previous excellent advice/answers.

Another option would be to connect the sheets once and have them part of a template structure.

• You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet or use cell-linking. The best option depends on if you have new data that would need to be connected or not.

To connect them row by row, you could use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you need in the Destination sheet.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

KPH

Dashboards are lagging behind sheets in terms of flexibility and ease of use. There is no "theme" you can set up and if you add your own colors to the palette you need to do this on every dashboard - it isn't remembered (which for me means having a post-it with color codes on!). It is improving though. There was a recent update that allows you to edit multiple widgets at once so you don't need to set the colors for every single graph. But I believe, you still need to do it for every dashboard and each time you add a single new graph.

One thing that may help you 🤞 is if you set up your basic project plan sheets and dashboards and then use them as a template for all the subsequent ones. That would save you building dashboards entirely from scratch each time. You would only need to edit them. It also has the advantage that there will be a lot of commonality across projects which will enable you to harness the power of the reports.

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?

thewell

Good question. I'll give it a shot and see if that works better. I'm open to trying anything, especially since I'm trying to get our team to adopt this more widely, and the heavy reliance on a manual set-up process is going to be a huge barrier to that objective.

Debbie Sawyer

@thewell

Great description and thread.

I have several ideas to help you with some of the items you mention, but to be honest it would be much easier to speak to you and screen share to talk you through them.

If you have time to work through some options with me, then please reach out.

Kind regards

Debbie Sawyer - Chief Smartsheet Solutions Officer (CSSO)

debbie.sawyer@smarterbusinessprocesses.com

 

James Keuning

I have more columns: one for each status option (not started, on hold, in progress, etc.) 

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 

These are problems. You need to normalize your data. "not started," "on hold," and "in progress" should not be columns. They should be values within one Status column.

Check this out:

A Step-By-Step Guide to Normalization in DBMS With Examples (databasestar.com)

thewell

https://community.smartsheet.com/discussion/comment/403065#Comment_403065

I agree that they're a problem, but it seems that they must be set that way for the donut graphs to work. See what I wrote for step 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.

thewell

https://community.smartsheet.com/discussion/comment/403064#Comment_403064

I'll bear that in mind. I would prefer to avoid screensharing or setting up a call at this juncture. I appreciate the kind offer, but this is my work computer, and I'm concerned about information security with such an approach. I'll investigate KPH's proposal, and if I continue to hit a dead end, I may reach out.

Debbie Sawyer

HI @James Keuning

I do think that @thewell is using just 1 column for Status, but they also have extra columns (one for each value) to act as an "in sheet" statistics. They mention using countifs within those extra columns, but I do believe they are using a Single Status column with multiple values as a dropdown list.

I think the issue with the metric grid is in the method of doing the cross sheet function, rather than the layout.

But it's all in the interpretation of the original thread!

With this one, I think one needs to see the sheets in question and be told the aim of the metric sheet and then offer guidance on layout.

@thewell I understand the reluctance for screen share with work data you can always set up a new workspace, do a Save as New on your sheet, (to create a copy) replace the data with some dummy data and then we can work on that together....

Kind regards

Debbie

James Keuning

https://community.smartsheet.com/discussion/comment/403069#Comment_403069

In this situation, I use another sheet and "pivot" the data using COUNTIF.

Andrée Starå

Hi @thewell

I hope you're well and safe!

To add to previous excellent advice/answers.

Another option would be to connect the sheets once and have them part of a template structure.

• You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet or use cell-linking. The best option depends on if you have new data that would need to be connected or not.

To connect them row by row, you could use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you need in the Destination sheet.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

thewell

https://community.smartsheet.com/discussion/comment/403153#Comment_403153

You and I landed on the same solution around the same time. INDEX / MATCH solved that first problem!

That's one down. Any thoughts on these two issues?

-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.

-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?

KPH

Dashboards are lagging behind sheets in terms of flexibility and ease of use. There is no "theme" you can set up and if you add your own colors to the palette you need to do this on every dashboard - it isn't remembered (which for me means having a post-it with color codes on!). It is improving though. There was a recent update that allows you to edit multiple widgets at once so you don't need to set the colors for every single graph. But I believe, you still need to do it for every dashboard and each time you add a single new graph.

One thing that may help you 🤞 is if you set up your basic project plan sheets and dashboards and then use them as a template for all the subsequent ones. That would save you building dashboards entirely from scratch each time. You would only need to edit them. It also has the advantage that there will be a lot of commonality across projects which will enable you to harness the power of the reports.

thewell

https://community.smartsheet.com/discussion/comment/403172#Comment_403172

Yeah, that's what I had in mind was to build a template and just use that.

Any thoughts on this last item? Seriously, that pop up drives me crazy because it always seems to appear when I'm deep in the zone and completely blows my concentration apart.

-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?

KPH

@thewell TBH the pop up doesn't bother me. I can't really say I notice it very often. Since your first message I have been thinking about why. There are two things I do that might be of interest.

1. Create a workspace and copy it for each project as we already discussed. I believe this will mean you won't see it for each copy.
2. I don't actually put contacts directly into my project plan - they are all referenced from other sheet. This enables me to have a template where tasks are assigned to roles rather than people. I then have another sheet where people are assigned to roles. And a formula that brings the contact in. For me, the advantages are:

• I don't hard code people into my templates and then have to change them if they move on.
• I can have one role carrying out 50 tasks and another carrying out 20 and all I need to do is enter their names into one place rather than against every task.
•