Show next pending task in Summary

Hello,

I am trying to show the next pending (open) task in Summary, so it will show on my Summary report as well. In the summary report I also want to show the delivery date for that task.

Basically if the previous task is Completed (checked), then I want the next one to show in Summary.

I am trying an IF and VLOOKUP formula but its not working.

Any ideas please?

Thanks a lot!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are your tasks in chronological order, or do we need to account for predecessors and dependencies? Are you able to provide a screenshot for reference?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • No predecessors or dependencies.

    I want to show the active task (and the delivery date) on my summary report.

    Thanks.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this...

    =INDEX(Milestone:Milestone, MATCH("In Progress", [Current Status]:[Current Status], 0))


    =INDEX([Initial Delivery date]:[Initial Delivery Date], MATCH("In Progress", [Current Status]:[Current Status], 0))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hi Paul, thanks.

    The first one worked, but I prefer to use the 'Completed' column as the trigger instead of Current status. Reason is because I may have tasks that are not in progress, but I still want to show them as "Next Milestone" in the report.

    I am trying this, but is not working

    =INDEX(Milestone:Milestone, MATCH("Checked", [Completed]:[Completed]))

    I want to do the same for the delivery date.

    Any ideas please?

    Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. Try this then...

    =INDEX(COLLECT(Milestone:Milestone, [Current Status]:[Current Status], @cell <> "", Completed:Completed, @cell <> 1), 1)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Many thanks, this worked.

    However its not working for the date:

    =INDEX(COLLECT([Initial delivery date]:[Initial delivery date], [Current Status]:[Current Status], @cell <> "", Completed:Completed, @cell <> 1), 1)

    Any ideas why?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!