excelexcel-formula

How to AutoFill using data in every third row?

=LEFT(A1,10)  
=LEFT(A4,10)  
=LEFT(A7,10)

I'd like to be able to AutoFill with the same spacing i.e.:

=LEFT(A10,10)  
=LEFT(A13,10)

When I try to AutoFill the rest of the values I get:

=LEFT(A4,10)  
=LEFT(A5,10)

How can I get the correct spacing?

Solution

  • Two ways to accomplish the desired output:

    =LEFT(CHOOSECOLS(WRAPROWS(A.:.A,3),1),10)

    or,

    =LEFT(INDEX(A:A,(ROW(A1)-1)*3+1),10)