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

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…