Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Is there a way to select from a project schedule the next upcoming milestone?

Phil Fuchs
edited 12/09/19 in Archived 2016 Posts

Is there a way to select from a project schedule the next upcoming milestone. I have a flag column identifyng milestones and a  column that calculates for just milestones the number of days between the task finish date and today's date and therfore the lowest value is the next upcoming milesone. I desire to pull the information (Task Name) of that task and move it to a specific row which is titled Next Upcoming Milestone.  From there I will have a Sight pointed to that row/cell and pull it into a dashboard. I am stumped having the system choosing the lowest value.  Anyone have experience doing what I am trying to accomplish?




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    If you are going to use a Sight, that actually makes it easier.


    1. Somewhere, you need this formula:


    =MIN([Days to MS]:[Days to MS])

    where the column name is Days to MS.

    It needs to be somewhere besides the Days to MS column or you will get a circular reference.

    This will tell you the shortest days to the MS..

    It won't tell you if there are more  than one, but that is taken care of below.


    2. Create a new column.

    I called mine VL MS

    In that column either of these will work:


    For a check box:

    =IF([Days to MS]14 = Info$1, 1, 0)



    For a text/number:

    =IF([Days to MS]13 = Info$1, [Task Name]13, "")


    where Info$1 is where the first formula was located.

    I have a column called Info where I store these odd one-off calculations.


    3. Create a Report

    Have the report show the Task Name column based on whether the new column has either (depending on which you used) a 1 (check box) or is not blank (text/number)


    4. Put the results of the Report into the Sight.


    And that should do it.





  • Phil Fuchs
    edited 04/12/16

    Craig, It worked perfectly.





  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
This discussion has been closed.