Returning Task Name based on a MIN(COLLECT(range, criteria_range, criteria)) formula

Ben Donahue

I am trying to get the task name of the current milestone to populate a cell so I can use that cell in a dashboard. I have a helper column that counts the number of days until due, and can get the lowest number in the range that is >0, no problem. How can I return the Task Name of the row that I find my lowest number > 0?

L_123

I have 3 solutions posted for you below:

 

1. Index Match

=index(TaskName:TaskName,match(min(Helper:Helper),Helper:Helper,0))

2. Index Collect

If you are deadset on using collect it would be something like below:

=index(collect(TaskName:TaskName,Helper:Helper,min(Helper:Helper)),1)

3. Join Collect

Both of these are going to have some issues if there are duplicate minimums in the helper column. If you are going to only have unique values in your helper column, I would use the first formula. If there are potentially multiple returns, you can use a variant of the second formula to concatenate your results.

=join(collect(TaskName:TaskName, Helper:Helper,min(Helper:Helper)),", ")

Ben Donahue

L,

After looking at that code you provided (Thank you, by the way), I don't believe it will grab the Task Name of the lowest number in Helper that is > 0. I Tried a variation of that here:

=INDEX(MIN(COLLECT(CHILDREN([Task Name]$22), CHILDREN([Next Task Due (days)]$22), >0)), 1)

This returns a zero, "0"

There are no zeros in the children of [Task Name]22. If I substitute [Next Task Due (days)]$22 for [Task Name]$22, it returns the correct lowest number > 0, so I know I am close. That code, of course, would look like this:

=INDEX(MIN(COLLECT(CHILDREN([Next Task Due (days)]22), CHILDREN([Next Task Due (days)]22), >0)), 1)

Your help is greatly appreciated.

-Ben

L_123

I believe you have misunderstood how the formulas work.

Collect can be treated as a range, the same way A1:A5 is treated inside of smartsheet. It just filters values out based on criteria you give it.

Min takes the lowest value of a range and returns a single integer

index takes a look at a range and returns the nth value

you are taking the index of a min with your formula.

Both of my collect formulas parse out the min using the min criteria inside of the collect instead of outside.

My original:

=index(collect(TaskName:TaskName,Helper:Helper,min(Helper:Helper)),1)

Goes to this with the references you gave me above:

=index(collect(CHILDREN([Task Name]$22),CHILDREN([Next Task Due (days)]22),min(CHILDREN([Next Task Due (days)]22))),1)

This collects all of the values in the children range of Task Name where the values of next task due days are equal to the minimum of all values in the same range. This means the collect should only ever return 1 value, but since it is in the range format we need to convert it to a value smartsheet can post. The index does this. (also the join)