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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @dc13

    I hope you're well and safe!

    Try something like this.

    =INDEX(CHILDREN(), MATCH(MAX(CHILDREN([email protected])), CHILDREN([email protected])))

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!


    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å | Workflow Consultant / CEO @ WORK BOLD

    W: | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.