Date cell referencing another date cell

Manu

Hi Folks

This should be trivial but I'm failing miserably. I have a gantt chart with start / finish date columns. I want to create a rolled-up milestone schedule at the very top by cherry picking some tasks in the schedule and displaying them again (an exact copy) in their own section at the top.

Sadly smartsheet will not let me make a date cell in a row = another date cell in a different row, which... I find very odd. I'm clearly missing something that should be obvious?

Paul Newcome

Do you have dependencies turned on? You cannot enter formulas into columns used by the dependency settings. You should be able to enter the formula(s) into other date columns not being used by the dependency settings.

How many tasks are you pulling? Sheet Summary may be an option.

Paul Newcome

Ah. Ok. I see what you are trying to do now. It makes sense. Unfortunately manual entry is going to be the way to go to have those included on the Gantt chart.

To keep on top of manual entry, you could include a flag or checkbox column that can be hidden after setup. That would contain a basic IF formula to check the box on the Summary Rows if the dates don't match. You could include an additional date column (also hidden after setup) that has a formula in it to pull the correct date.

Then you can setup an automated Update Request that shows you the hidden date column for whatever row doesn't match and you can update the manually entered summary date as needed from the Update Request form (since the proper date is included in the email, you wouldn't have to open the sheet to accomplish it either).

I will suggest that you Submit a Product Enhancement Request for using formulas in dependency columns in the mean time.

Paul Newcome

Do you have dependencies turned on? You cannot enter formulas into columns used by the dependency settings. You should be able to enter the formula(s) into other date columns not being used by the dependency settings.

How many tasks are you pulling? Sheet Summary may be an option.

Manu

Aha yeah that would be why, thanks Paul. Damn... and no using sheet summary would not work for me. Bummer.

Paul Newcome

Happy to help. 👍️

Are you running further calculations on those specific dates. or are they just for display? There MIGHT be other options.

Manu

Just for display. Is there a way to reference the entire row?

Paul Newcome

You could join the Task Name, Start Date, and End Date into one string and display it in a single column as text.

=[Task Name]## + ": " + [Start Date]## + " - " + [End Date]##

This would produce a result along the lines of

Task ABC: 6 Jan. 20 - 7 Feb. 20

(Of course the dates would maintain whatever format they are in for your date type column)

This can also be further automated if your list could change or you only want to display the next 5 that have the nearest due dates or many other options if your list is liable to change. If your list is going to remain static once it is set up, then the above should work well for you. The nice thing about the above is that since we are referencing cells, the dates will coincide with the original task row so that it truly is duplicated even if the dates change.

Manu

Right, but then I could not show the task as a milestone in my Gantt chart (with a black diamond), correct? which is what I'm trying to solve. Basically I have a complex Gantt chart that I want to distill to a rolled-up milestone section within the same chart. I know I could do it with a report, but that's a last resort, I do not want to solve this with a separate sheet if I can help it.

Milestone Summary

-- Task 12

-- Task 7

Section A

...

-- Task 12

....

Section B

...

-- Task 7

...

Paul Newcome

Ah. Ok. I see what you are trying to do now. It makes sense. Unfortunately manual entry is going to be the way to go to have those included on the Gantt chart.

To keep on top of manual entry, you could include a flag or checkbox column that can be hidden after setup. That would contain a basic IF formula to check the box on the Summary Rows if the dates don't match. You could include an additional date column (also hidden after setup) that has a formula in it to pull the correct date.

Then you can setup an automated Update Request that shows you the hidden date column for whatever row doesn't match and you can update the manually entered summary date as needed from the Update Request form (since the proper date is included in the email, you wouldn't have to open the sheet to accomplish it either).

I will suggest that you Submit a Product Enhancement Request for using formulas in dependency columns in the mean time.

Manu

Thank you Paul for your time, it's really much appreciated. I will submit an enhancement request.

Paul Newcome

Happy to help! 👍️

GoGators

I found a trick that admins can do. You can link the start date of a task (say a milestone-reference-task) to the end date of another task, even a task on another sheet. If you set the duration of that milestone-reference-task to 0d then the finish date is the same and there you have it!