How to have each subsequent value that matches a criterion appear in its own cell in a column?

Ann.Cracovaner

I'm trying to pull change order numbers that are assigned to one specific person in a source sheet appear as separate entries in one column in another sheet. I've gotten it to manually work with this formula:

=INDEX(COLLECT({CO Number}, {Assigned PM}, "PM Name"), 1)

But I have to manually adjust the number at the formula to 2, 3,4, etc. to get additional values to return in the next cell in the column.

I tried swapping the number for @row, but got back the #UNPARSEABLE error code.

Any thoughts on how to tweak this so I can make a column formula that will bring in the next value from the source sheet assigned to that person in perpetuity?

KPH

Hi @Ann.Cracovaner

It sounds like the value for the first instance of the CO Number for that PM needs to appear on row 1 in the sheet, the second on row 2, third on row 3, etc. If so, there is a solution.

While @row does not return the row number of the current row, with a couple of steps you can find it. You essentially need to use another formula to return the row number of the current row. To do this, first add an autonumber column. Then use a MATCH formula to return the row number of the row where the auto number matches the auto number in the current row. It is a bit of a mouthful.

But the formula is simple:

=MATCH([Auto number]@row , [Auto number]:[Auto number], 0)

Here this that formula in column 4.

Notice how it does not matter that the rows are moved around. Even though the autonumber shows the order the rows were added, the formula returns the number of the row they occupy. You can use that in your formula.

=INDEX(COLLECT({CO Number}, {Assigned PM}, "PM Name"), MATCH([Auto number]@row , [Auto number]:[Auto number], 0))

Hope this helps.