ROW reference automatically adjusting

[fredsunbay]

Good day all,

I've got the below formula which is working fine. I would like to drag the formula down and increase the last row reference, -ROW($B$1)-10),1)),""), with 1 so it becomes 2, 3, 4, etc.

=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),1)),"")

 

[StephenCrump]

Welcome to the Forum!

The usual way to do this is to use a ROWS() reference.

For example, if your formula starts in A1, say, replace 1 with ROWS(A$1:A1)

Copy down to A2, and the formula will become ROWS(A$1:A2) = 2, etc etc

 

[fredsunbay]

Welcome to the Forum!

The usual way to do this is to use a ROWS() reference.

For example, if your formula starts in A1, say, replace 1 with ROWS(A$1:A1)

Copy down to A2, and the formula will become ROWS(A$1:A2) = 2, etc etc

Thank you for your reply, in this case the suggest solution doesn’t work unfortunately.

 

[StephenCrump]

... the suggest solution doesn’t work ...

This doesn't convey any useful information to help us understand what's happening, or not happening.

Here's my layout:

	DL	DM
1
2
3
4
5
6
7
8
9
10
11
12
13	111	ABC
14
15	222	ABC
16
17
18
19
20	333	ABC
21
Day 1

	A	B	C	D	E
1	Find	ABC		You	Me
2				111	111
3				222	222
4				333	333
5
Sheet1

Cell Formulas
Range	Formula
D2,B4	D2	=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),1)),"")
E2:E5	E2	=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),ROWS(E$2:E2))),"")
D3	D3	=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),2)),"")
D4	D4	=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),3)),"")
D5	D5	=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),4)),"")

I didn't pay attention to the rest of your formula previously, but I see now that:

=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),ROWS(E$2:E2))),"")

would better be written as:

=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW('Day 1'!$DM$12)+1),ROWS(E$2:E2))),"")

to provide protection against rows being inserted/deleted at the top of the "Day 1" sheet.

 

[fredsunbay]

This doesn't convey any useful information to help us understand what's happening, or not happening.

Here's my layout:

	DL	DM
1
2
3
4
5
6
7
8
9
10
11
12
13	111	ABC
14
15	222	ABC
16
17
18
19
20	333	ABC
21
Day 1

	A	B	C	D	E
1	Find	ABC		You	Me
2				111	111
3				222	222
4				333	333
5
Sheet1

Cell Formulas
Range	Formula
D2,B4	D2	=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),1)),"")
E2:E5	E2	=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),ROWS(E$2:E2))),"")
D3	D3	=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),2)),"")
D4	D4	=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),3)),"")
D5	D5	=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),4)),"")

I didn't pay attention to the rest of your formula previously, but I see now that:

=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW($B$1)-10),ROWS(E$2:E2))),"")

would better be written as:

=IFERROR(INDEX('Day 1'!$DL$12:$DL$199,SMALL(IF($B$1='Day 1'!$DM$12:$DM$199,ROW('Day 1'!$DM$12:$DM$199)-ROW('Day 1'!$DM$12)+1),ROWS(E$2:E2))),"")

to provide protection against rows being inserted/deleted at the top of the "Day 1" sheet.

Your adjustment works like a charm !!

Thank you so much, you've saved me hours of extra work . . .

 

[StephenCrump]

Great, I'm glad it's working for you, and thanks for letting us know.