Auto-generate due dates for project tasks in multiple projects

Kate Allison

Hi!

I am looking for a solution to help automate due dates for a large set of projects, all which have the same set of tasks.

Some details: I have dozens of projects, all with the same set of tasks, and they're all tracked in 1 sheet (see template below, with each project listed as a parent row named 'product').

I'd like to find an easier, repeatable way for the sheet to auto-generate due dates for each task, based on a due date I enter in the "product" row. In addition, I want the task due dates to adjust if I move one task due date earlier/later.

Where I've gotten stuck: I've played around with creating predecessors & task durations, and I can get it to work for 1 project. However, I've only been able to manually set predecessors for each project. With 50+ projects to track, this isn't a realistic solution for me.

Instead, I'm hoping Smartsheet is smart enough to understand that, for example, while a task in Project 1 has a predecessor of row 5, the same task in Project 2 has a predecessor of row 42. Is there a way to create a template, or some other solution, that achieves this each time I create a new project? Could the solution be applied to my existing projects, or would I need to rebuild each one?

Thank you for any help!

Mike Wilday

One way we have accomplished this is by creating a template row at the top of our sheet with all of the sub-rows already created. You can open up all of the sub-rows and then copy and paste those into your sheet to create a new project.

We used to do that method, but we transitioned to using a separate sheet per project because at a certain point that many rows became way overwhelming.

Susan Peck

@Kate Allison

Hi Kate,

We have a similar situation and have used formulas that reference a cell with a source date. You can add the references to your template set of tasks and copy them along with the tasks. Smartsheet picks up the relative location (or specific name) of the referenced cell. In your formula, you can add days to or subtract days from the reference date.

For example: =([Ref Date in Parent Row] + 2] results in the date for the specific task being two days after the date in the Parent Row.

Hope this helps a little? I can provide more detail if you want.

susan

Mike Wilday

Susan's solution would work as well but if your template rows are set up with dependencies they will retain their data when you copy and paste them too.

Kate Allison

Thanks, @Mike Wilday! Did you find that when copying the template row information it automatically adjusted the predecessor column to match the row numbers for the new project? Or did the template's predecessor row numbers remain the same ?