INDEX Collect to reference another sheet

sstelmack

I want to use index collect to get who worked on a job from a different sheet

=INDEX(COLLECT([ASSIGNED ENGINEER]@row, [JOB NAME]@row, <>"", [JOB NAME]@row, <>""), 1)

This works on the same sheet but when I try to apply it to another sheet

=INDEX(COLLECT({APN/DFA COMPLETED Range 3}, [JOB NAME]@row, <>"", {APN/DFA COMPLETED Range 4}, <>""), 1)

it does not work any ideas?

Adam Murphy

You want to compare the remote {Job Name} to the [Job Name]@row. The collect should be COLLECT(<field I want to return>, range to search 1, criteria 1, range to search 2, criteria 2…).