Cross Sheet References

kira11

Hello,

I have a tasks list sheet with a parent row as the top level PO # and the children are the subtasks and various line items under the PO #.

I want to create a sheet that grabs the DRI person for each subtask but only once at the top level PO (the person will always be the same for the various line item #s under one PO). Essentially so it would look like this:

Is there an easy formula to do this? I can't seem to get an index/match type to work since there are two criteria.

Thanks!

Jennifer Kurtz

Hi @kira11 - I think you could use an Index(Collect here; that will let you use multiple criteria.

Basic syntax would be =INDEX(COLLECT(Range for DRI Person, Criterion_Range1, Criterion1, Criterion_Range2, Criterion2….), 1)

Does that help?

kira11

I was thinking that too but can't get it right I don't think. Essentially I would have:

=INDEX(COLLECT({DRI}, {PO}, [PO #]@row, {Task Name}, "Contract Review"), 1) but this gives me #INCORRECT ARGUMENT SET. Any ideas?

Jennifer Kurtz

@kira11 -

Well, your formula looks good — the syntax looks right!

My best guess is maybe your ranges aren't set quite right? (Sometimes when setting those ranges, the system "refreshes" or something in the middle and ends up selecting the top left cell. It's thrown off my ranges many times….!)