Return task name associated with a future date

06/05/19 Edited 12/09/19

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

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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()))), ", ")

    thinkspi.com

  • edited 06/07/19

    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

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Grace,

    Did you get it working?

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 07/12/19

    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.

     

    thinkspi.com

  • Tori GreenTori Green ✭✭✭✭✭

    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)))


Sign In or Register to comment.