Return task name associated with a future date

Options
Grace N
Grace N ✭
edited 12/09/19 in Formulas and Functions

I have a project with linked tasks. I can get the start date of the next task using the following formula:

=MIN(COLLECT(Start2:Start6, Start2:Start6, >TODAY()))

How can I return the task name associated with this upcoming task?

In this example, this formula gives the next start date as 6/19/19. What formula will return ‘Task 3’?

Thanks. I’ve learned a lot from this community

20190605 Test timeline.png

Tags:
· Share on FacebookShare on Twitter

Comments

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

    You could use an INDEX/MATCH formula, but it will only return the first task name if there are multiples. It would look something like this...

     

    =INDEX([Task Name]2:[Task Name]6, MATCH(Start2:Start6, MIN(COLLECT(Start2:Start6, Start2:Start6, >TODAY())), 0))

     

    If you wanted to join all of the tasks together if there are multiples, you could use a JOIN/COLLECT formula like this...

     

    =JOIN(COLLECT([Task Name]2:Task Name]6, Start2:Start6, MIN(COLLECT(Start2:Start6, Start2:Start6, >TODAY()))), ", ")

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Grace N
    Grace N ✭
    edited 06/07/19
    Options

    Thanks but when I pasted your formula into my sheet, I get an INCORRECT ARGUMENT SET message. 

    20190606a Test timeline 1.png

    20190606 Test timeline 1.png

    · Share on FacebookShare on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi Grace,

    Did you get it working?

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 07/12/19
    Options

    Sorry for the delay. I just now saw this. It looks like it's a syntax issue within your MATCH.

     

    You have

     

    MATCH(range, what to look for.....

     

    It should be

     

    MATCH(what to look for, range......

    .

    Switch your MIN(COLLECT( to the first portion of the MATCH statement, and that should clear it up for you.

    .

    EDIT

    I just realized you entered the formula exactly as I had specified, so that's on me. My apologies.

     

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Tori Green
    Tori Green ✭✭✭
    Options

    I'm also having difficulty with the Syntax, trying to get the data in the Deadline cell to populate into a summary for the next upcoming deadline. Here is my formula: =INDEX(Deadline1:Deadline16, MATCH([Due Date]1:[Due Date]16, MIN(COLLECT([Due Date]1:[Due Date]16, [Due Date]1:[Due Date]16 > TODAY()), 0)))

    image.png


    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!