Returning Task Name based on a minimum number in Helper that is >0
I have previously asked a similar question, but with fewer requirements.
I am trying to return the Task Name associated with the lowest number in a helper column that is >0.
With some help from L@123, I was able to get close, I think, except the following code returns a zero, and there are no zeros in CHILDREN([Task Name]$22):
=INDEX(MIN(COLLECT(CHILDREN([Task Name]$22), CHILDREN([Helper]$22), >0)), 1)
Your help and instruction is greatly appreciated.
Comments

I wonder if its counting blank cells as 0's? Can you check your data and make sure every cell has a number? Maybe work it out on a test sheet?

There are no blank cells in scope. I haven't yet tried the test sheet approach. I did, for reasons I can't fathom nor remember, try this piece of code that worked, for reasons of which I am unaware:
=INDEX(COLLECT(CHILDREN([Task Name]$22), CHILDREN(Helper$22), >0), 1)
Here is my thinking (please feel free to correct my understanding as it strays from reality):
The COLLECT function will return, in this case, a list or array; multiple datums in the single return, anyway. The INDEX function is defaulting to the first entry, which, since the data is structured in the sheet in ascending order, is always the MIN, so it works...in this instance with this prearranged data structure. 
Paul,
I am getting a #NESTED CRITERIA error. I will eventually need a JOIN, I believe, because, at some point, the return will contain two datums, as one date will mark two milestones, so I am very interested in seeing this through. I will get into parsing out what your code is saying later today.
Here is the code that eventually worked for me:
=INDEX(COLLECT(CHILDREN([Task Name]$22), CHILDREN(Helper$22), >0), 1)
I asked Mike to chime in on my (mis) understanding of why this code is working, and I very much invite your thoughts on this:
Here is my thinking (please feel free to correct my understanding as it strays from reality):
The COLLECT function will return, in this case, a list or array; multiple datums in the single return, anyway. The INDEX function is defaulting to the first entry, which, since the data is structured in the sheet in ascending order, is always the MIN, so it works...in this instance with this prearranged data structure.
I also have no idea why the code I posed in the original post is returning a zero, so, any thoughts you have on that would be enlightening, too.
Thank you.

Your first formula is returning a zero because you have the main return being the MIN function. MIN will always return a number, so since there are no numbers in your Task Name column, it is going to return a zero.
I am looking into the nested criteria error now to see if there is a way around that.

You are correct in your thinking about the INDEX formula. It is pulling the MIN because you have the sheet sorted so that the lowest number is at the top.
If you were to replace the 0 in your helper column with some kind of text, even "0" or the text "zero", would that cause issues elsewhere?

I added a string and adjusted the date such that it appears before the next scheduled real, not test, event. The result was unchanged. So, the formula only found numerical entries, skipping the string entirely, as is shown in the second screen shot.
So, to answer your question directly, no, it seems not to have any effect.

Ok. So if you replace ONLY the number zero with some kind of text, you will then be able to remove the COLLECT function from the MIN which is where the nested criteria error was coming from.
So leave all of the numbers above zero as numerical values and change your zeros to some form of a text string.
Then you can use
=JOIN(COLLECT(CHILDREN([Task Name]$22), CHILDREN(Helper$22), @cell = MIN(CHILDREN(Helper$22))), ", ")

I see. It seems I didn't at all know how the INDEX formula worked. I thought that the index of the returned MIN would be the Task Name, being the Primary Identifier. Of course, indices are always integers, so, my bad on that.
Now I understand, I think, that
=INDEX(MIN(COLLECT(CHILDREN([Task Name]$22), CHILDREN([Helper]$22), >0)), 1)
will look through the children of Helper22, return the Task Name entries associated with the children of Helper22 that are >0, find the minimum numerical value of this list of strings (which defaults to 0), then, with a range of the number 0 (which corresponds to no columns) it will return the first entry in that range, essentially an empty set.
Is that right?

Using CHILDREN([Task Name]$22) as the range to COLLECT from is why your MIN is returning a zero. If there are no numbers within a range, MIN will default to 0.
Using MIN(COLLECT( means you are collecting for the MIN function when in all reality you are wanting to COLLECT for the INDEX function.
=INDEX(COLLECT(CHILDREN([Task Name]$22]), CHILDREN(Helper$22), @cell = ...............................)
.
The problem is that you will need an additional COLLECT function in the MIN where the .................. is to specify the MIN of everything over 0.
That COLLECT inside of the COLLECT is where the #NESTED CRITERIA error occurs.
You also stated that you would eventually need a JOIN function because there could be more than 1 task within the range that matches that lowest number above zero.
.
So the solution would be to remove the numerical value of zero from the helper column altogether so that we can remove the nested criteria issue of having to COLLECT inside of the COLLECT.
.
Then we replace the INDEX with a JOIN and swap the row number with a delimiter, and that should work for you.
.
How are the numbers in your Helper column being populated?
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!