Date cell referencing another date cell

Options
Manu
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?

Best Answers

Answers

  • Manu
    Manu ✭✭✭✭
    Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Happy to help. 👍️


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

  • Manu
    Manu ✭✭✭✭
    Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 01/08/20
    Options

    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
    Manu ✭✭✭✭
    Options

    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

    ...

  • Manu
    Manu ✭✭✭✭
    Options

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

  • GoGators
    GoGators ✭✭✭
    Options

    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!