Skipping a cell when copying down a formula, cannot get ROW(1:1) to skip to (ROW(2:2)

[alexs24]

I am trying to figure out how to skip a cell when copying down a formula, I have to fill the formula down around 2000 rows to cannot do so manually. Specifically in cell B2 I have the formula:

INDEX(Sheet1!$BV$3:$BV$311,SMALL(IF($A2=Sheet1!$BV$3:$BV$311,ROW(Sheet1!$BV$3:$BV$311)-ROW(Sheet1!$BV$3)+1),ROW(1:1))))

I want to skip cell B3 and have the following formula automatically copied into cell B4:

INDEX(Sheet1!$BV$3:$BV$311,SMALL(IF($A2=Sheet1!$BV$3:$BV$311,ROW(Sheet1!$BV$3:$BV$311)-ROW(Sheet1!$BV$3)+1),ROW(2:2))))

What is happening now is I cannot get the ROW formula at the end from changing when I copy the formula down. So cell B4 is always coming up as:

INDEX(Sheet1!$BV$3:$BV$311,SMALL(IF($A4=Sheet1!$BV$3:$BV$311,ROW(Sheet1!$BV$3:$BV$311)-ROW(Sheet1!$BV$3)+1),ROW(3:3))))

[martindwilson]

CEILING(ROW(1:1)/2,1) would give
1
1
2
2
3
3
but you would still have a formula in every row just repeated twice

[Tony Valko]

See if you can adapt this example.

Data Range

	B	C	D	E	F	G
1	------	------	------	------	------	------
2	1				x	1
3					x	2
4	2				h	5
5					j	4
6	3				x	3
7					d	2
8	4				x	4
9

This array formula** entered in B2 and copied down:

=IF(MOD(ROWS(B$2:B2),2)=0,"",INDEX(G:G,SMALL(IF(F$2:F$8="x",ROW(F$2:F$8)),CEILING(ROWS(B$2:B2)/2,1))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.