Index(Collect Across 8 Sheets

System

This discussion was created from comments split from: Using a nested IF to determine a range.

ShannonL

@Paul Newcome I'm looking to achieve something similar but with multiple ranges. I have a unique key that's the same in multiple sheets and im trying to index a specific code from those sheets based on a criteria in each sheet. I was thinking I could you COLLECT but I don't want ALL the criteria to be true, just 1 of the criteria. How would this like look with multiple ranges?

Paul Newcome

@ShannonL Are you able to provide screenshots for context?

ShannonL

@Paul Newcome Sure. I want to populate Task Codes from other sheets. There are 8 sheets with different Task Codes on each sheet. The project # is the unique key on all sheets. The CHILD (Project Name) is what the formula should reference on each sheet to retrieve the Task Code that I want to INDEX. I've created a Range reference for each Tack Code so I can insert the reference into the formula. The formula below is what I have so far but I need to modify it so once I convert it to a column formula it will index the appropriate task code associated with the Project Child name. Thanks for your help.

Paul Newcome

@ShannonL Where exactly are you wanting this formula to go/which rows will it be applied to once converted to column formula?

ShannonL

@Paul Newcome It should go in the Task Code Column and it will be applied to all CHILD rows in green.

Paul Newcome

@ShannonL What about screenshots of your reference sheet?

ShannonL

@Paul Newcome See below. The other 7 sheets are the same just with a different activity and different task code.

Paul Newcome

@ShannonL So to be clear... You want to bring in the [Task Code] from another sheet based on the [Project Name]@row as well as PARENT([Project #]@row?

In that case you will need an INDEX/COLLECT.

=INDEX(COLLECT({Task Code}, {Activity}, @cell = [Project Name]@row, {Project #}, @cell = PARENT([Project #]@row), 1)

ShannonL

@Paul Newcome This feels like the right direction. I appreciate the insight.

How do I collect all ranges in a single formula? Would I just need to duplicate this portion -> {Task Code}, {Activity}, @cell = [Project Name]@row, {Project #}, @cell = PARENT([Project #]...for each "Activity" and "TaskCode" I want it to retrieve? Similar to a nested COLLECT formula? The goal is to make this a column formula so that when new rows are added it will populate automatically. Thanks.

Paul Newcome

It should work without having to add anything to it.

ShannonL

@Paul Newcome

Hey circling back on this thread and need some clarification. I input the formula above and am getting and "Incorrect Argument" Error (see screenshot).

My question is, how do I COLLECT the "Task Code" and "Activity" ranges in all 8 sheets with only one reference? When I create a reference, it only allows me to reference a range on one sheet. The Task Code and Activity I need to collect are on 8 separate sheets so essentially, I would have to create 8 different ranges. Ex: {TaskCode1}, {TaskCode2}, {TaskCode3}, etc... {Activity1}, {Activity2}, {Activity3}, etc...

The above for formula doesn't appear to account for that.

Any clarity on how to update this formula would be appreciated. Thanks.

Formula trying to use:

=INDEX(COLLECT({Task Code}, {Activity}, @cell = [Project Name]@row, {Project #}, @cell = PARENT([Project #]@row), 1)

Genevieve P.

Hi @ShannonL

It looks like you're missing an extra parentheses, after the PARENT() function but before the 1 that resides in the INDEX function.

E.g:

=INDEX(COLLECT(...PARENT(...)), 1)

Try this for 1 sheet:

=INDEX(COLLECT({Task Code}, {Activity}, @cell = [Project Name]@row, {Project #}, @cell = PARENT([Project #]@row)), 1)

In regards to 8 sheets, you'll need 8 separate formulas as each reference will need to be done per-sheet:

=INDEX(COLLECT({Task Code - Sheet 2}, {Activity - Sheet 2}, @cell = [Project Name]@row, {Project # - Sheet 2}, @cell = PARENT([Project #]@row)), 1)

If you need all results in the same cell, you could add them together:

=INDEX(COLLECT(first sheet)) + " / " + INDEX(COLLECT(second sheet)) + " / " + INDEX(COLLECT(third sheet))

and so on.

Cheers,

Genevieve