Dependency Workaround: Linked End Date, Not Duration

KatrinaRJ

I'm linking to someone's Start Date on a project, using the relevant sheet. However, the End Dates I need to track are at the end of a long set of tasks on another sheet - only once all those tasks are completed will this person's time on the project be done. I'd like to link to the very last task this person will perform and have Smartsheet do the math on how long they're working in total. I understand that I cannot currently link an "End Date" from one sheet to another, because the "value is determined by a project dependency." But I'd love the ability to shift the dependency from the End Date column to the Duration column, which is the column I need to auto calculate.

Is there a workaround for this issue? I saw in another forum post that someone mentioned creating another End Date column and linking it to there, but that doesn't solve the issue since the original End Date is blank, thus my duration is blank. Or do I need to disable dependencies, link my Start and End, and use a Duration Formula to calculate it? 

 

Thanks!

Paul Newcome

Are you trying to calculate an actual or a projected date?

KatrinaRJ

It's an actual date, not projected. Thanks!

Paul Newcome

I'm sorry. I am kind of lost on what you are trying to do. Are you able to post some screenshots with an example in there?

KatrinaRJ

Hi Paul - 

I've attached two screenshots. The first shows the row where I'd like to link the "End" cell (cell colored yellow for illustration purposes) to another Sheet, called Events. This row shows when someone is wrapping, and I'd like to link it to my Events Sheet so that changes to the events this person is working on update their End date correctly. 

However, because I have dependencies enabled in this sheet, I cannot link the End date - I have to link the Duration instead (see the error message in the screengrab). Linking the Duration doesn't work for me, because as you can see in my second screengrab, this person's wrap date is at the end of a long series of short tasks going all the way into 2020. There's no single Duration cell I could link to on the Events page, so instead, I need to link to the last date (6/12/20). 

It seems to be an immutable fact that I cannot link to a date when dependencies are enabled; only a duration. (This feels like an issue Smartsheet should fix....) What I'm looking for is a way around this, a workaround that allows me to accomplish the same end goal. 

Let me know what you think, thanks for your help! 

Paul Newcome

Since you can only link to a duration... Can you sum the duration column for all cells that have no children (ignores the "summary rows") then use that duration to establish your end date?

 

I tested this particular solution. I needed a helper column (checkbox type) and used 

 

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, 1)

 

I then used

 

=SUMIFS(Duration:Duration, Helper:Helper, 1)