In Progress Formula Not Working (Also Start Date Not Adjusting)

Jeff Casto

Hello,

We had a project template set up for us that would automatically provide a status of "In Progress" if the % Complete was not 0. I made the % Complete 10% for one of the tasks and the parent didn't pick it up, so the status did not move to "In Progress".

Here's a screenshot of the task being 10% complete and the parent showing "Not Started"

This screenshot also shows that I made the first task date "8/13/24" and the start date parent didn't change from what it picks up from the project metadata, but the project metadata pick up from the project sheet, which I tried to change the date of, but it didn't change…it's a weird circle.

Here's a screenshot of the formula for the Status Parent

Does anyone have any idea why

1. The Status doesn't change to "In Progress" when a task is assigned a % complete?
2. Why the start date parent doesn't change when I change it on the project sheet (I know it's pulling from project metadata, but that is pulling from project sheet, which I changed…oy)

Thank you!

Matt Lynn-PCG

@Jeff Casto So the way you're doing your structure/rollup is good/common. There's one thing I could offer to improve it is to organize your summary data in a way so that you're able to paste a range of cell links for any amount of summary rollup values you need vs doing them one by one. Much faster/easier.

From what I can see your issue is, not really an issue. Your orange row "Trafficking" has a date of 7/29.

The way the dates work is the start date (In the blue parent "project name" row is going to always show the earliest date from all children and the end/finish date will always show the latest. Since the orange row is 7/29, that's the earliest date, the blue parent row is showing that. That's how it's supposed to work.

Now if you don't want that to be the case I'd suggest that your predecessor column doesn't have the "Trafficking" row linked right because you probably want ALL items to go out or in based on your kickoff/discovery row. So point: The only the wrong is that your predecssors probably aren't pushing trafficking (and maybe other rows) correctly. I suspect if you were to manually change the dates of the orange row then you'd see the parent row move.

If you're interested I could hop on a 15 min chat with you to help look at these items.

Matt Lynn-PCG

@Jeff Casto a couple things to clear up… these have a couple areas of tricky to them usually.

1.) Do you have dependency turned on in the gantt settings that would allow the auto calcs to occur as you indicate you want?

2.) I see there are more children than just "Kickoff/Discovery" to that parent. If (hypothetically) you have 10 children and the other 9 are at 0% still, the average at the parent may still be .01 and rounding to 0 or something similar, thus not triggering the if() statement.

3.) If you aren't using dependency to allow the auto rollup you'd need to add a avg(children()) formula to that % complete cell, but hopefully you are using the dependency.

Jeff Casto

Hello @Matt Lynn-PCG !

Thank you soooo much! Your suggestion #2 cleared up the Status "In Progress" Issue.

As for the start date parent not changing, I believe dependencies is activated. Here are some screenshots showing that I think. Does this help? I still can't get the parent to budge…

Jeff Casto

Hi again! Not sure this helps, but I was looking at the information sources and it's a little tricky.

First of all, just to explain more what we are doing…this project plan sheet is one we are using as a template for all incoming projects. It is not a Smartsheet "Template", but one we use as a "Save as New" file that serves as a template. So, each time a project is created, the folder that this project plan is a part of is copied into a new Workspace.

Secondly, when I look at the source of this parent date that isn't budging, it is coming from the Project Metadata sheet - screenshot here:

And when I go to the Project Metadata sheet to see the source of that date, it says it's coming from the above project sheet…so, it's a strange circle and I can't figure out how to change it. Screenshot here:

Another wierd thing is, in the project plan sheet, I changed the first date (kickoff and discovery) to 7/31 and the above parent still says 7/29)

Maybe there's a way I can have the new project plans start at the start date that is requested and not consistently 7/29?

Hmmm…Thanks for any further help!

Matt Lynn-PCG

@Jeff Casto sorry for the delay in the response.

So there are several things going on here and I'm a hands on guy so not sure I will cover all that needs to be covered in response. But: your save as new process is fine, do it all the time if not within a control center structure… and that's basically what control center does anyway… and other stuff.

So it's not a circle because the data is going one direction… the plan shows "Link to…." and the metadata shows "link in"… so doesn't look like a circle.. and that's a good thing. The metadata sheet shouldn't have any issues here assuming it's also within that template folder you copy. If it's outside of that folder that would be a problem. All the issues here seem to be on the schedule itself.

Are the rows from "Kickoff/Discovery" down to "Client Final/Approval Due" ALL the children of the parent row or are there others also?

There's definitely ways to set it up to work with a new kickoff date but it seems the issue you have still needs to be fixed unless I'm missing something. Also some people utilize two sets of date columns. One being planned dates and one being actuals but the issue here is only one set can be setup with dependencies etc. That can help in some cases you just have to be aware of the +/- benefits of that option.

Jeff Casto

Hi @Matt Lynn-PCG ! No sorries, I appreciate any help at all and I'm still fining out more about it as we speak (as I didn't design it, I'm just backward engineering).

Thanks for the clarification on it not going in a circle…I think that was just my head spinning!

We definitely have this project in a project folder for now, which includes a bunch of other stuff we don't necessarily need (other than the project sheet itself and the metadata sheet).

Our main goal is to have a Portfolio level dashboard that reports on all projects. The basic process for incoming jobs is:

1. User fills out intake form
2. I copy the project level folder template from within the PMO workspace and save that project level folder to a Client workspace.
3. I then take the project ID from the project intake sheet and add it to the project metadata for that new project. The new project metadata then autopoulates with the data from the project intake sheet
4. I then cell link the line from the project metadata sheet to the Portfolio Summary Rollup (That makes it so I can see the new project in the Portfolio Dashboard)
5. Then what I want to do is reset the baseline for that project to the new start date…and the parent start date doesn't want to move past 7/29/24 - which is something I just discovered! I can change the dates and the project sheet responds up until 7/29/24, but if I choose any date after, the parent date doesn't budge.

I have a screen recording, but can't post it here, so here are some screenshots…

Regarding all the children, we do have other children, they are all phases with their own set of children tasks:

I have no idea if this helps…but, there's a hidden "Helper" column it seems I just unhid all columns).

Anyways, thanks for all the great thoughts Matt, I'll keep kicking it around until I get it to work!!!

Matt Lynn-PCG

@Jeff Casto So the way you're doing your structure/rollup is good/common. There's one thing I could offer to improve it is to organize your summary data in a way so that you're able to paste a range of cell links for any amount of summary rollup values you need vs doing them one by one. Much faster/easier.

From what I can see your issue is, not really an issue. Your orange row "Trafficking" has a date of 7/29.

The way the dates work is the start date (In the blue parent "project name" row is going to always show the earliest date from all children and the end/finish date will always show the latest. Since the orange row is 7/29, that's the earliest date, the blue parent row is showing that. That's how it's supposed to work.

Now if you don't want that to be the case I'd suggest that your predecessor column doesn't have the "Trafficking" row linked right because you probably want ALL items to go out or in based on your kickoff/discovery row. So point: The only the wrong is that your predecssors probably aren't pushing trafficking (and maybe other rows) correctly. I suspect if you were to manually change the dates of the orange row then you'd see the parent row move.

If you're interested I could hop on a 15 min chat with you to help look at these items.

Jeff Casto

Thank you so much @Matt Lynn-PCG ! This is great information to know. And you solved my mystery! Not to bore you to tears (so stop reading now), but it's semi-interesting because the "Trafficking" phase is based on options from the previous stage. So, my solution will be when I open a project, I will manually adjust the predecessor in Trafficking based on the requested target start and end date for the project.

Again, many many thanks!!!!

Matt Lynn-PCG

@Jeff Casto Schedules and Preds are a interesting thing to work with with multiple options. I've done some where you have a line item (Let's say "Grand opening date" for something like a retail store). That may be the last row of the schedule that you populate manually (or automatically if you're using control center etc.), but that item is a reverse predecessor to the previous ones so that you don't have to change predecessors, only dates. Just an idea… with many alternative options.