CODEWITHSUNDEEP
google-sheets
jms2k
jms2k

Reputation: 81

Increment row, not column of spreadsheet

I am working on a spreadsheet that tracks my running mileage. I've got all my miles in one column, but I want to be able to create a "Calendar View" of sorts, so I can track my miles on Mondays, Tuesdays, etc.

To do this, I am using: =Mileage!C13 for cell b2. For cell B3, I want: =Mileage!C14.

Obviously, I will be using 7 columns for the calendar, but when I try to create a series in the row, it increases the column (instead of C14, it gives D13).

Also, I tried doing a series in the column instead, and increasing by 7 with no luck. B2: =Mileage!C13. B3=Mileage!C20. If I select both cells and try to create a series, it gives me =Mileage!C14 instead of =Mileage!C27.

I hope this makes sense... any tips, or am I going about this completely wrong? Thanks!

Upvotes: 1

Views: 2793

Answers (1)

AdamL
AdamL

Reputation: 24659

You can reproduce the first 7 cells in a row fairly easily with the TRANSPOSE function:

=TRANSPOSE(Mileage!C13:C19)

However this still doesn't address the issue of being able to easily fill the formula down. Using OFFSET, we can rewrite the above formula to:

=TRANSPOSE(OFFSET(Mileage!C$13,0,0,7,1))

and then use the ROW function and a bit of maths in the second argument:

=TRANSPOSE(OFFSET(Mileage!C$13,(ROW(A1)-1)*7,0,7,1))

This formula you should now be able to fill/drag down successfully.

Upvotes: 2

Related Questions