COMPLEX INDEX/MATCH arguments issue: Project Updates Tasklist

@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)

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!