Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
SteveB
 
Posts: n/a
Default Relative reference autofill increment other than +1

I have often needed to drag and autofill a formula such that row references
increment by something more than 1. For example, consider the formula
"=sum(A1:A10)" in cell B1. If I have 100 rows of data in column A from A1 to
A100 and I want to roll up sums in groups of 10 starting in B1, I would drag
my formula down from B1 through to B10. The problem is that the autofill
increment will increment A1 to A2 and read "=sum(A2:A11)" and so on down the
rows. What I want the row reference increment to be is by 10 so the next
formula in B2 would read "=sum(A11:A20)" and so on down through B10 reading
"=sum(A91:A100).

Any ideas for how to control the row increment to something other than +1
when dragging down? I'll write a VBA procedure if I have to, but wonder if
there isn't something built in for this.

A solution to this would also have a complementary use for incrementing
columns in an autofill.
  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

Enter this formula in any cell in row 1 and copy down as needed:

=SUM(OFFSET(A$1,(ROW()-1)*10,,10))

B1 =Sum(A1:A10)
B2 =Sum(A11:A20)
B3 =Sum(A21:A30)
etc
etc

Biff

"SteveB" wrote in message
...
I have often needed to drag and autofill a formula such that row references
increment by something more than 1. For example, consider the formula
"=sum(A1:A10)" in cell B1. If I have 100 rows of data in column A from A1
to
A100 and I want to roll up sums in groups of 10 starting in B1, I would
drag
my formula down from B1 through to B10. The problem is that the autofill
increment will increment A1 to A2 and read "=sum(A2:A11)" and so on down
the
rows. What I want the row reference increment to be is by 10 so the next
formula in B2 would read "=sum(A11:A20)" and so on down through B10
reading
"=sum(A91:A100).

Any ideas for how to control the row increment to something other than +1
when dragging down? I'll write a VBA procedure if I have to, but wonder
if
there isn't something built in for this.

A solution to this would also have a complementary use for incrementing
columns in an autofill.



  #3   Report Post  
SteveB
 
Posts: n/a
Default

Looks good and compact. I intend to code this into a VBA procedure for
right-click access to automate it for rows and columns. I'll use it a lot.

Thank you very much.

"Biff" wrote:

Hi!

Enter this formula in any cell in row 1 and copy down as needed:

=SUM(OFFSET(A$1,(ROW()-1)*10,,10))

B1 =Sum(A1:A10)
B2 =Sum(A11:A20)
B3 =Sum(A21:A30)
etc
etc

Biff

"SteveB" wrote in message
...
I have often needed to drag and autofill a formula such that row references
increment by something more than 1. For example, consider the formula
"=sum(A1:A10)" in cell B1. If I have 100 rows of data in column A from A1
to
A100 and I want to roll up sums in groups of 10 starting in B1, I would
drag
my formula down from B1 through to B10. The problem is that the autofill
increment will increment A1 to A2 and read "=sum(A2:A11)" and so on down
the
rows. What I want the row reference increment to be is by 10 so the next
formula in B2 would read "=sum(A11:A20)" and so on down through B10
reading
"=sum(A91:A100).

Any ideas for how to control the row increment to something other than +1
when dragging down? I'll write a VBA procedure if I have to, but wonder
if
there isn't something built in for this.

A solution to this would also have a complementary use for incrementing
columns in an autofill.




  #4   Report Post  
Biff
 
Posts: n/a
Default

One point to note,

The formula I posted is row specific and must be entered in a cell in row 1.

This formula is not row specific and can be entered anywhere in the sheet:

=SUM(OFFSET(A$1,(ROW(1:1)-1)*10,,10))

Biff

"SteveB" wrote in message
...
Looks good and compact. I intend to code this into a VBA procedure for
right-click access to automate it for rows and columns. I'll use it a
lot.

Thank you very much.

"Biff" wrote:

Hi!

Enter this formula in any cell in row 1 and copy down as needed:

=SUM(OFFSET(A$1,(ROW()-1)*10,,10))

B1 =Sum(A1:A10)
B2 =Sum(A11:A20)
B3 =Sum(A21:A30)
etc
etc

Biff

"SteveB" wrote in message
...
I have often needed to drag and autofill a formula such that row
references
increment by something more than 1. For example, consider the formula
"=sum(A1:A10)" in cell B1. If I have 100 rows of data in column A from
A1
to
A100 and I want to roll up sums in groups of 10 starting in B1, I would
drag
my formula down from B1 through to B10. The problem is that the
autofill
increment will increment A1 to A2 and read "=sum(A2:A11)" and so on
down
the
rows. What I want the row reference increment to be is by 10 so the
next
formula in B2 would read "=sum(A11:A20)" and so on down through B10
reading
"=sum(A91:A100).

Any ideas for how to control the row increment to something other than
+1
when dragging down? I'll write a VBA procedure if I have to, but
wonder
if
there isn't something built in for this.

A solution to this would also have a complementary use for incrementing
columns in an autofill.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I change a cell from absolute reference to relative referen simonsez Excel Discussion (Misc queries) 1 May 17th 05 08:39 PM
Row reference increment but preserve column reference Pwanda Excel Worksheet Functions 1 April 28th 05 01:12 PM
"relative reference" question newarkj Excel Discussion (Misc queries) 2 April 27th 05 06:05 PM
changing multiple cells from relative to absolute reference Mike Excel Discussion (Misc queries) 4 March 10th 05 03:11 PM
Relative worksheet reference in 3-D formulas? [email protected] Excel Worksheet Functions 13 January 15th 05 04:01 PM


All times are GMT +1. The time now is 10:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"