CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Tyler Gerig
Tyler Gerig

Reputation: 15

Problem with incrementing of a cell in a google sheets offset formula

I have three formulas, relatively the same, but with a different column for each.

=OFFSET('War Layouts'!$B$2,(ROW(B1)-1)*7,0)
=OFFSET('War Layouts'!$F$2,(ROW(F1)-1)*7,0)
=OFFSET('War Layouts'!$J$2,(ROW(J1)-1)*7,0)

The formula's do exactly what I want, but I want to copy them down my column to repeat the functionality.

When I copy them down, the row number changes to (B4),(F4),(J4), etc. And nothing else increases, which is what I want.

I want the row value to be (B2),(F2),(J2) when I copy down the formula and I just cannot wrack my brain on how to achieve this.

Edit: For additional information, i'm providing a spreadsheet example of what i'm working with.

https://docs.google.com/spreadsheets/d/1Fxc4KcAYlh7vfg7UV0oestKdyuf0LrpvzJmyt0JbAzg/edit?usp=sharing

Essentially I want pull the 'Name' Field, 3 time's a week, every 7 rows. So day 1 is the B column, Day 2 is F, Day 3 is J.

On the 'Stats' Sheet, is where I have the formula's above. The first 3 row's are correct, but the next three have B4,F4,J4 instead of B2,F2,J2, etc.

Upvotes: 1

Views: 1111

Answers (1)

kirkg13
kirkg13

Reputation: 3010

This may do what you want, similar to what WOUNDEDStevenJones has described. Since dragging down will normally increment the row numbers in the equations by 1, use INDIRECT to calculate the new row number, based on the current row number divided by 3. The only trick is factoring in your starting row. Here are the three new formulae to replace your existing formulae:

=OFFSET($B$2, ROW( INDIRECT("B" &    INT((row()+2)/3)))  *7  ,  0)
=OFFSET($F$2, ROW( INDIRECT("F" &    INT((row()+2)/3)))  *7  ,  0)
=OFFSET($J$2, ROW( INDIRECT("J" &    INT((row()+2)/3)))  *7  ,  0)

If you put these anywhere on rows 1,2 and 3 (one above the other), they can be dragged down as a group of three. You can test it out by taking just one part, =ROW( INDIRECT("B" & INT((row()+2)/3))), and dragging this down. You'll see it increments the row number by one every three rows.

The only trick is getting the starting value right. If you don't want to place the first formula in row 1, you need to adjust the "+2" amount downwards by 1, for each row you move down. So +1 if starting on row 2, +0 if on 3, -1 if on 4, etc.

Let me know if this helps, or if you need more explanation.

Upvotes: 0

Related Questions