Changing AutoFill cell reference (WindowsXP, Office 2003)

pssureshbabu

New Member
Joined
Oct 5, 2006
Messages
16
Hello all!

I notice my AutoFill option do like this:

=SUM(A1:A5)

After dragging down using autofill, the result is

=SUM(A2:A6)
=SUM(A3:A7)

is it possible to program autofill to do the following?

=SUM(A6:A10)
=SUM(A11:A15)

and so on...

thanks for your help,

Babu
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You would need a formula like these:

=SUM(INDEX(A:A,((ROW(A1)-ROW(A$1))*5)+1):INDEX(A:A,((ROW(A1)-ROW(A$1))*5)+5))

=SUM(OFFSET(A$1,(ROW(A1)-ROW(A$1))*5,0,5))

The INDEX formula is longer, but it's not volatile like OFFSET is.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,058
Messages
5,545,757
Members
410,704
Latest member
Cobber2008
Top