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

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?

Find more posts tagged with

Healthcare

Formulas

IT & Operations

Comments

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

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)

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…