Return task name associated with a future date
![Grace N](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
Comments
-
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()))), ", ")
-
Thanks but when I pasted your formula into my sheet, I get an INCORRECT ARGUMENT SET message.
-
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.
-
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.
-
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)))
Help Article Resources
Categories
Check out the Formula Handbook template!