Pulling Down array Formula - Need To Change absolute reference

Hi, I have the following array formula

Formula:

=IFERROR(INDEX($F$2:$F$22,SMALL(IF($F$2:$F$22<>"",ROW($F$2:$F$22)-1),COLUMN(A:A))),"")

which I've to pull down 100 rows.
I have kept the reference locked because it has to be pulled across columns also.

Presently, I am manually editing the reference for each row ( Eg: changing F2:F22 to G2:G22) which is taking a lot of time.

Please provide a solution


Regards

Could you not just leave the column references absolute while making the row references relative?

I'm attaching the file so that its clear.

Try

in AA2


=IFERROR(INDEX($F$2:$Y$22,SMALL(IF(INDEX($F$2:$Y$22,,ROWS($F$2:F2))<>"",ROW($F$2:$F$22)-1),COLUMN(A:A)),ROWS($F$2:F2)),"")

Originally Posted by JohnTopley

Try

in AA2


=IFERROR(INDEX($F$2:$Y$22,SMALL(IF(INDEX($F$2:$Y$22,,ROWS($F$2:F2))<>"",ROW($F$2:$F$22)-1),COLUMN(A:A)),ROWS($F$2:F2)),"")

excellent!!!
Thanks a lot JohnTopley


Could you give a small explanation?

=IFERROR(INDEX($F$2:$Y$22,SMALL(IF(INDEX($F$2:$Y$22,,ROWS($F$2:F2))<>"",ROW($F$2:$F$22)-1),COLUMN(A:A)),ROWS($F$2:F2)),"")

INDEX($F$2:$Y$22............

selects the total range .... with ROWS($F$2:F2) being the COLUMN number so we select column F [ ROWS($F$2:F2)=1 ], then G [ ROWS($F$2:F3)=2] ..etc


INDEX($F$2:$Y$22,,ROWS($F$2:F2))<>"",ROW($F$2:$F$22)-1),COLUMN(A:A))

the highlighted again part selects the COLUMN of the INDEX range so as the formula is copied down ROWS($F$2:F2) is incremented by 1 so we get the first column in the range - F, then the second - G ...etc.

COLUMN(A:A)) is the index for the SMALL: as the formula is dragged across columns this has values of 1, 2, 3 etc.

Hope this helps.

Thanks a lot.

You're welcome and thank you for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.