how to create a custom auto fill sequence

Hi,

I understand that excel can auto fill by recognizing patterns like 1,2,3,4 or 2,4,6,8. My question is can excel recognize complex patterns?

I would like B1 to B10 = to A1 and then B11 to B20 = A2 and then B21 to B30 = A3. From there I am trying to drag the fill handle down column B hoping that it will auto fill the rest. But when i do try to auto fill B31 begins by equaling to A31 and B32 to A32 etc. Is there a way to create a formula or edit the auto fill option so that it will follow my pattern?

Thanks in advance for any tips.

Originally Posted by benishiryo

hi lix333, welcome to the forum. try this formula in B1:
=INDIRECT("A"&FLOOR(ROW(A10)/10,1))

Wow!

1. Thank you for responding so fast.
2. Thanks for the formula. I think this is exactly what i have been looking for!

Many many thanks. This just saved me days of work.

Originally Posted by benishiryo

hi lix333, welcome to the forum. try this formula in B1:
=INDIRECT("A"&FLOOR(ROW(A10)/10,1))

Well, I thought I was done but my spreadsheet became a bit more complex.

Is there a way to also go horizontally to the next column? so B1 to B10 = A1 and then C1 to C10 = A2 and then d1 to d10 = A3 then highlight A1 to A3 and drag to custom auto fill downward so that b11 to b20 = A4 and then C11 to C20 = A5 and then D11 to D20 = A6 so on and so on.


If this is possible then thanks in advance!

Originally Posted by benishiryo

you're welcome. for your new requirements, try this in B1:
=OFFSET($A$1,COLUMN(A1)-1+FLOOR((ROW(A1)-1)/10,1)*3,0)

Hey thanks for this! I actually found an alternative formula for my column.

If anyone is still reading this thread. Is it possible to that the excel file becomes so thick with coding that it wont work properly or just becomes really slow and sluggish? Is there a general rule of how much code you can use per file?