COMPLEX INDEX/MATCH arguments issue: Project Updates Tasklist

dc13

@Paul Newcome @Paul Newcome I need your expertise

My manager has tasked me with referencing data from a hierarchy of rows, for multiple columns, but wanting the Parent Row to be updated from the inputs of the data in the CHILDREN rows, which he then is asking for the most recent update of that selected customer. I am needing the PARENT row to update automatically whenever a CHILD row has been updated or added. My thought process has been to use the INDEX/MATCH function to pull the specific return cell I need, but the issue that I cannot wrap my head around is how to pull the MATCH of the most recent update for the modified column and to INDEX the Project Rating Status of that most recently updated CHILD row.

I am having a world of trouble trying to find out the best way to use these nested functions to automate my Parent rows.

If someone could lead me in the right direction?

I have had many variations of this unfinished function to pull a specific input but my data has always read as #UNPARSEABLE.

See below for overly complicated function:

INDEX(CHILDREN([Project Rating Status]1):CHILDREN([Modified]1),MATCH(MAX(COLLECT(CHILDREN([Modified]1), CHILDREN([Project Rating Status]1), @cell <>"")), CHILDREN([Project Rating Status]):CHILDREN([Modified]1),0), CHILDREN([Project Rating Status]1)

Andrée Starå

Hi @dc13

I hope you're well and safe!

Try something like this.

=INDEX(CHILDREN(), MATCH(MAX(CHILDREN(Modified@row)), CHILDREN(Modified@row)))

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @dc13

I hope you're well and safe!

Try something like this.

=INDEX(CHILDREN(), MATCH(MAX(CHILDREN(Modified@row)), CHILDREN(Modified@row)))

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Andree's suggestion should work. The only thing different that I would do is to add the "0" in the MATCH function to ensure we are getting the exact match.

=INDEX(CHILDREN(), MATCH(MAX(CHILDREN(Modified@row)), CHILDREN(Modified@row), 0))

Andrée Starå

@Paul Newcome

Thanks! Good catch!