increment formula by 1 after 3 rows with drag function

[nd4spd]

hi i presume this is something simple but cant seem to figure out

how can i keep formula in 3 rows same & increment by 1 thereafter using drag down.
test file attached

thank you

[Glenn Kennedy]

One way;

=INDEX(data!$A$2:$A$6,1+INT((ROWS(A$2:A2)-1)/3))

[nd4spd]

Excellent Thank you Glenn

[José Augusto]

Try

Please Login or Register  to view this content.

[Glenn Kennedy]

You're welcome and thanks for the rep.

[nd4spd]

Thank you José what would i need to alter in "=INDIRECT("data!A"&INT((ROW(A2)+1)/3)+1) " to increment after 4 rows

[nd4spd]

Try

Please Login or Register  to view this content.

Thank you José what would i need to alter in "=INDIRECT("data!A"&INT((ROW(A2)+1)/3)+1) " to increment after 4 rows

[José Augusto]

For step 4 try

Please Login or Register  to view this content.

[Glenn Kennedy]

Comment removed by GK

[Glenn Kennedy]

Just for fun... I ran the two formulae suggested above, and another old favourite of mine (based on OFFSET, another volatile function) through shg's formula speed checker. The results were only partly as i had expected.

=INDIRECT("'data'!A"&INT((ROW(A2)-2)/3)+1)
=INDEX(data!A$1:$A$20,1+INT((ROWS(D$2:D2)-1)/3))
=OFFSET(data!$A$1,INT((ROWS($A$2:A2)-1)/3),,,)

based on 20 data cells and each formula copied down 60 rows.

The INDIRECT formula was (as I had expected) slowest. It's also volatile and will recalculate every time something changes on the sheet.

The INDEX formula was faster (almost exactly 3 times faster than INDIRECT), again, much as I had expected. It has the advantage of being non-volatile and does not recalculate.

The surprise was the OFFSET formula. It was faster than INDEX, clocking in at almost exactly 4 times faster than INDIRECT. I guess it ultimately loses out as a result of its volatility, but I had not expected it to be the fastest to calculate.

On a learning curve...

Edit: Since one of the big advantages of INDIRECT is its ability to use a cell reference as a source for the target sheet name, I re-ran the speed test, picking up the sheet name (data) from a cell reference, rather than being hard coded in the formula. It was almost exactly as fast (or as slow... depending on you PoV) as the original INDIRECT formula. Formula used: =INDIRECT("'"&$J$8&"'!A"&INT((ROW(H2)-2)/3)+1)