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

Ben Donahue
Ben Donahue ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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?

Comments

  • L_123
    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
    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
    L_123 ✭✭✭✭✭✭
    edited 10/22/19

    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!