Help with Index Match bringing back Children row data based on Parent Row Match

Help with Index Match bringing back Children row data based on Parent Row Match

How do I get Index/Match to do the following:

Look at Source sheet column for a Match in a Parent row and if there is a match, pull in two date columns for all the CHILDREN.

This formula is pulling in dates but only for the first PARENT/CHILD section. It's not checking the MATCH on the Parent Asset #.

=INDEX(COLLECT({Test AMT Task Started}, {Test Parent}, PARENT([Deliverables and Tasks]@row), {Test AMT Asset/Tasks}, [Deliverables and Tasks]@row), 1)

This formula say's Unparseable.

=(INDEX(COLLECT({Test AMT Task Started}, {Test Parent}, PARENT([Deliverables and Tasks]@row), {Test AMT Asset/Tasks}, [Deliverables and Tasks]@row), MATCH(PARENT({Test AMT Asset #}, {Asset #}, 1))))

Thanks!!

Answers

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Jeana,

    Unfortunately, it doesn't work with parents.

    The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS.

    I hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Bummer! Thanks for the confirmation. Now to ponder a work around. :-)

    Jeana

  • So if I eliminate the PARENT/CHILD relationship my sheet looks like this:

    My goal is to pull in the ACTUAL START DATE and ACTUAL END DATE into this sheet based on the Asset # and Deliverables and Tasks (this is what is unique).

    I've tried several variations of INDEX COLLECT but can't seem to get it right.

    =INDEX(COLLECT({Test AMT Task Started}, MATCH(COLLECT([Asset #]@row, [Deliverables and Tasks]@row), {Asset #}, {Test AMT Asset/Tasks}, 0)))


  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Jeana

    I'd be happy to take a quick look.

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • There are 7 stages:


    Build array Child containing every name.

    Sort array Child. I have provided a simple sort which is adequate for a demonstration. Better sorts are available on the internet if you have enough names to require it.

    Build array Parent such that Parent(N) is the index within Child of the parent of Child(N).

    Build array ParentName by following the pointers in array Parent from child to parent to grandparent to ... While doing this, determine the maximum number of levels.

    Sort array ParentName.

    Build a header row in the output sheet.

    Copy ParentName to the output sheet.

    I believe I have included enough comments for the code to be understandable.


    Option Explicit

    Sub CreateParentChildSheet()


     Dim Child() As String

     Dim ChildCrnt As String

     Dim InxChildCrnt As Long

     Dim InxChildMax As Long

     Dim InxParentCrnt As Long

     Dim LevelCrnt As Long

     Dim LevelMax As Long

     Dim Parent() As Long

     Dim ParentName() As String

     Dim ParentNameCrnt As String

     Dim ParentSplit() As String

     Dim RowCrnt As Long

     Dim RowLast As Long


     With Worksheets("Sheet2")

      RowLast = .Cells(Rows.Count, 1).End(xlUp).Row

      ' If row 1 contains column headings, if every child has one parent

      ' and the ultimate ancester is recorded as having a parent of "Root",

      ' there will be one child per row

      ReDim Child(1 To RowLast - 1)


      InxChildMax = 0

      For RowCrnt = 2 To RowLast

       ChildCrnt = .Cells(RowCrnt, 1).Value

       If LCase(ChildCrnt) <> "root" Then

        Call AddKeyToArray(Child, ChildCrnt, InxChildMax)

       End If

       ChildCrnt = .Cells(RowCrnt, 2).Value

       If LCase(ChildCrnt) <> "root" Then

        Call AddKeyToArray(Child, ChildCrnt, InxChildMax)

       End If

      Next

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @lewis hamilton

    Your solution is for Excel and not Smartsheet so that it won't work, unfortunately.

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.