Cross Sheet References

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!

Find more posts tagged with

Formulas

Accepted answers

All comments

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….!)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.