Cross sheet referencing, index collect for a range

KLo_

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?

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.

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.