Is it possible to have multiple return ranges located in multiple forms with INDEX COLLECT?

Jordan1990

For example, pulling from one other sheet will use the following:

=index(collect(return RANGE, criteria RANGE 1, criteria 1, ,1)

I am looking for the INDEX COLLECT function to return values from multiple sheets depending on if the criteria range matches my @row. This will be the only criteria I search on.

Thanks!

Find more posts tagged with

Accepted answers

Jeff Reisman

@Jordan1990

It's possible to use several INDEX/COLLECTS inside of nested IF statements. For example:

=IF(Name@row = "Jeff", INDEX(COLLECT(return RANGE, criteria RANGE 1, criteria 1, ,1), IF(Name@row = "Charlotte", INDEX(COLLECT(return RANGE2, criteria RANGE 2, criteria 2, ,1), ""))

All comments

Jeff Reisman

@Jordan1990

It's possible to use several INDEX/COLLECTS inside of nested IF statements. For example:

=IF(Name@row = "Jeff", INDEX(COLLECT(return RANGE, criteria RANGE 1, criteria 1, ,1), IF(Name@row = "Charlotte", INDEX(COLLECT(return RANGE2, criteria RANGE 2, criteria 2, ,1), ""))

Jordan1990

Thank you, this makes sense however I am still receiving an error message. Instead of clicking the reference another sheet button for each INDEX statement, I merely changed the Sheet name in the formula text string. Think that could be my issue?

Jordan1990

Yup looks like I had to enter in each sheet link using the "reference another sheet" link for each one. No worries, this is all fixed now. Thanks!!!!

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.