Forum Discussion
Solved
Range changing in increments
I need some help. I’m summarising data from one sheet into another, the amount of data points is in the thousands. In my summary sheet I’m summarising around 250 data rows into one row on a seperate s...
Let's say the formula =SUM(DataSheet!B2:B138) is in cell D2 of the summary sheet.
Change it to
=SUM(OFFSET(DataSheet!$B$2:$B$138, 137*(ROW()-2), 0))
or
=SUM(INDEX(DataSheet!B:B,137*(ROW()-2)+2):INDEX(DataSheet!B:B,137*(ROW()-2)+138))
Then fill down.
Perfect. Can you explain the row situation. And would it be similar if I wanted to do the same along the columns for data going across?
Never mind found out I just have to take the $ off the B but leave it on the number
Great!
ROW() returns the row number of the cell with the formula. So for a cell in row 2, ROW() returns 2.
ROW()-2 returns 0 in row 2, 1 in row 3 etc.
137*(ROW()-2) returns 0 in row 2, 137 in row 3, etc.
OFFSET(DataSheet!B$2:B$138, 137*(ROW()-2), 0) is equivalent to B$2:B$138 in row 2, but to B$139:B$275 (that is, 137 rows down from B$2:B$137) in row 3, etc.