Index(Collect Across 8 Sheets

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

Answers

  • ShannonL
    ShannonL ✭✭✭
    edited 06/05/23

    @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
    Paul Newcome ✭✭✭✭✭✭
  • ShannonL
    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
    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
    ShannonL ✭✭✭

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


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • ShannonL
    ShannonL ✭✭✭

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


  • Paul Newcome
    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
    ShannonL ✭✭✭
    edited 06/07/23

    @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
    Paul Newcome ✭✭✭✭✭✭

    It should work without having to add anything to it.

  • ShannonL
    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.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!