Using fill but jumping from M to R to W instead of M to N to O

[eric247]

I have a list of formulas getting data from sheet two.

='Sheet2'!M10
='Sheet2'!M16
='Sheet2'!M72
='Sheet2'!M74
='Sheet2'!M56

I want to fill to the right so the next column is

='Sheet2'!R10
='Sheet2'!R16
='Sheet2'!R72
='Sheet2'!R74
='Sheet2'!R56

and the next column is

='Sheet2'!W10
='Sheet2'!W16
='Sheet2'!W72
='Sheet2'!W74
='Sheet2'!W56

and so on, skipping the same number of columns each time.

All I can get it to give me when I fill is
='Sheet2'!N10
='Sheet2'!N16
='Sheet2'!N72
='Sheet2'!N74
='Sheet2'!N56

and

='Sheet2'!O10
='Sheet2'!O16
='Sheet2'!O72
='Sheet2'!O74
='Sheet2'!O56

Which is not what a want. How do I get fill to change the sheet2 column that it's referencing into (M, R, W, AB, AG, AL etc.) instead of (M, N, O, P, Q, R etc.) as I fill to the right?

 

[njimack]

Try replacing your first formula with the following (you'll need to amend each formula so that they refer to the correct row)
=INDIRECT(ADDRESS(10,COLUMN(M1),,,"Sheet2"))

 

[eric247]

I'm fairly new to Excel so perhaps I misunderstood something, but when I type the formula you gave me in column M and change the rows accordingly as such

=INDIRECT(ADDRESS(10,COLUMN(M1),,,"Sheet2"))
=INDIRECT(ADDRESS(16,COLUMN(M1),,,"Sheet2"))
=INDIRECT(ADDRESS(72,COLUMN(M1),,,"Sheet2"))
=INDIRECT(ADDRESS(74,COLUMN(M1),,,"Sheet2"))
=INDIRECT(ADDRESS(56,COLUMN(M1),,,"Sheet2"))

and then fill to the right I still get in column N:

=INDIRECT(ADDRESS(10,COLUMN(N1),,,"Sheet2"))
=INDIRECT(ADDRESS(16,COLUMN(N1),,,"Sheet2"))
=INDIRECT(ADDRESS(72,COLUMN(N1),,,"Sheet2"))
=INDIRECT(ADDRESS(74,COLUMN(N1),,,"Sheet2"))
=INDIRECT(ADDRESS(56,COLUMN(N1),,,"Sheet2"))

I want this in column N:

=INDIRECT(ADDRESS(10,COLUMN(R1),,,"Sheet2"))
=INDIRECT(ADDRESS(16,COLUMN(R1),,,"Sheet2"))
=INDIRECT(ADDRESS(72,COLUMN(R1),,,"Sheet2"))
=INDIRECT(ADDRESS(74,COLUMN(R1),,,"Sheet2"))
=INDIRECT(ADDRESS(56,COLUMN(R1),,,"Sheet2"))

and I want to be able to repeat that pattern (going 5 columns to the right each time) without manually changing the columns.

Does your formula fix this and I just manipulated it incorrectly or misunderstood something?

 

[jasonb75]

How do I get fill to change the sheet2 column that it's referencing into (M, R, W, AB, AG, AL etc.)

A non-volatile alternative.

=INDEX(Sheet2!$M10:$AL$10,,(COLUMNS($M$1:M$1)-1)*5+1)

 

[njimack]

My bad - try this:
=INDIRECT(ADDRESS(10,COLUMN(A1)+(COLUMN(A1)*12)-(COLUMN(A1)-1)*8,,,"Sheet2"))

 

[eric247]

That worked, thanks!