How to use Index(Collect(....

EBloohm

I'm trying to build a summary sheet that will pull values from a task list.

How do I have the values in row 1 of the summary sheet reference the corresponding values under the 12345 parent.

I think that INDEX and COLLECT are the tools I need but I don't know where to start.

Paul Newcome

You will first need a helper column on the source sheet that brings the 12345 onto every row.

=IF(COUNT(ANCESTORS([Part Number]@row)) = 0, [Part Number]@row, INDEX(ANCESTORS([Part Number]@row), 1))

Then you INDEX/COLLECT would be along the lines of

=INDEX(COLLECT({Checkbox Column}, {Helper Column}, @cell = [Part Number]@row, {Part Number Column}, @cell = "Drawings"), 1)

Paul Newcome

You will first need a helper column on the source sheet that brings the 12345 onto every row.

=IF(COUNT(ANCESTORS([Part Number]@row)) = 0, [Part Number]@row, INDEX(ANCESTORS([Part Number]@row), 1))

Then you INDEX/COLLECT would be along the lines of

=INDEX(COLLECT({Checkbox Column}, {Helper Column}, @cell = [Part Number]@row, {Part Number Column}, @cell = "Drawings"), 1)