Cross sheet referencing, index collect for a range

I have a source sheet of different items, rolling out on different dates for different stores. I am creating a target sheet of a rollout of one specific item. I want the target sheet to list all the stores on the source sheet, that meet the criteria. The formula below, works but for one row (one store).

INDEX(COLLECT({SOURCE SHEET STOREID}, {Spring Beverage Master Rollout Info Range 2}, "Y"), 1)

I want this to work for as many rows match the criteria. For this instance there are 136.

I got the join collect to work for all instances but it puts all the store numbers in one cell.

JOIN(COLLECT({SOURCE SHEET STOREID}, {Spring Beverage Master Rollout Info Range 2}, "Y"), ",").

Is there a way to return one store number for each row on my target sheet?

Find more posts tagged with

Accepted answers

KLo_

Got it to work with this formula:

IFERROR(INDEX(COLLECT({SOURCE SHEET STOREID}, {Spring Beverage Master Rollout Info Range 2}, "Y"), [Number]@row), "")

Paul Newcome

The helper column goes on the same sheet as the INDEX/COLLECT.

All comments

Paul Newcome

You would need to insert a helper column (called "Number" in this example) and manually populate as many numbers as you think you will need plus I recommend a bit of a buffer. You say there are 136, so I would go to 200.

Then you would change the 1 in your INDEX/COLLECT to

Number@row

This will pull the first, second, third, etc..

To make rows 137 - 200 blank until they are needed, you would wrap the whole ting in an IFERROR.

=IFERROR(INDEX?COLLECT(……………), Number@row), "")

KLo_

#UNPARSABLE:

=IFERROR(INDEX(COLLECT({SOURCE SHEET STOREID}, {Spring Beverage Master Rollout Info Range 2}, "Y"), number@row), "")

The helper column is on the source sheet - right? How does the formula know it is on another sheet?

KLo_

Got it to work with this formula:

IFERROR(INDEX(COLLECT({SOURCE SHEET STOREID}, {Spring Beverage Master Rollout Info Range 2}, "Y"), [Number]@row), "")

Paul Newcome

The helper column goes on the same sheet as the INDEX/COLLECT.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.