Creating custom autofill with numerical values

Question

Each day of the year has a value which I would like to consolidate by day of the week, i.e. all 52 Mondays consecutively, all 52 Tuesdays consecutively etc. They are recorded chronologically on Sheet 1, and when organizing them by weekday on the consecutive sheet, the one that I made for 2013 (and can't remember for the life of me how I got it to do this automatically) looks like:

A     B
Mon   =Sheet1!O7
Mon   =Sheet1!O14
Mon   =Sheet11O21

...

Tue   =Sheet!O8
Tue   =Sheet!O15
Tue   =Sheet!O22

My question is how do I autofill with a specific pattern? The weekdays for 2014 are shifted backwards by one, so the first Monday of the year would let's say be O6 now instead of O7 and unless I remember how to copy a formula but minus one from the cell reference then the formulas are off by a day. If I try selecting =Sheet!O6 and =Sheet!O13 and dragging to autofill, instead of adding seven and making the next cells =Sheet!O20 and =Sheet!O27, it makes them =Sheet!O8 and =Sheet!O15, so like:

=Sheet!O6
=Sheet!O13
=Sheet!O8
=Sheet!O15
=Sheet!O10
=Sheet!O17

instead of:

=Sheet!O6
=Sheet!O13
=Sheet!O20
=Sheet!O27
=Sheet!O34
=Sheet!O41

How do I get the above?

Answer 1

You can use an indirect like:

=INDIRECT("Sheet2!O"&ROW()*7-1)

The 7 is your increment, and the -1 is your initial offset (start at 6 on this). The O of course is the column number. Then you can drag this down.

You could also use an index to do the same type of thing.