Reference a row by Task Name instead of by the row Number.

Here is my challenge. I would like to create a formula in a template based on a row's Task Name (Task Name is a column), not the actual Row Number. The Row Number for this Task Name may change from proejct to project so I cannot reference the Row Number.

The formula is pretty simple. I have a Completion Date column and a Start Date column. When a Completion Date is entered in one row, I want the Start Date populated with that same date in another row.

The task list is very simple with no dependencies.

Any ideas?

Thank you for your help!

Find more posts tagged with

Templates

Formulas

Conditional Formatting

Comments

John Hammond

Is there a reason why you are not using dependencies? If you have them enabled all you would need to do is add predecessors and the start date for a task will automatically calculate based on the end date of the proceeding task. This sounds like exactly what you are looking for.

If you dont wan to use predecessors...

If you do not have dependencies enabled on your sheet, you can add formulas to your date column. This formula will can be added to the start date cell and will show the date of the referenced cell (completion date of the previous task):

=[Completion Date]5

Megan Heinz

Thanks for the reply. That is the formula I have been using.

I think you are right, I will play around with dependencies. I was shying away form them but I think I have enough uses now that I need to.

It looks like dependencies are based on the row number as well, but we will see.

Megan Heinz

Ok so I have a quick follow-up question. I want the dependency to just fill the Start Date for the dependent task, not the Completion Date. Isn't that against the point? I am missing something.

John Hammond

If you add a predecessor, the start date, end date, and duration will be added if none of them already exists. The end date is calculated based on the start date and duration. If you put a TBD in the duration column, the end date will not be added (but the start date will) when you add a predecessor value. Just remember an end date is calculate to calcualte the start date of a subsequent task.