Cut and Paste using r1C1

Question

I am using Excel2013.

I have a secondary worksheet which references a raw data worksheet as follows:

AverageIntrinsic	AverageAdaptivity
6.666666667	5.666666667

The reference for the values in cells A1 and A2 above point to the raw data worksheet Data!C1 and Data!C2.  The reference for the values in B1 and B2 point to Data!AZ1 and Data!AZ2.

I am trying to create "pairs".  So that every column (beginning with C1 in the raw data, always has to the right of it, Average Adaptability (Data!AZ2).  Another way of saying this, is that I want to interleave Average Adaptability between each of the existing columns in the raw data.

Ideally, the next pair would be Data!D1 in C1 above and Data!D2 in C2 above....followed by the constant Data!AZ1 and Data!AZ2 in column D as the second part of the "pair".

However, when copying these 4 cells (A1:B2) to C1, the reference in the raw data jumps 2 columns, to Data!E1 and Data!E2.  (I am holding the absolute Data!$AZ$1 and Data!$AZ$2 constant.

I do not want to jump 2,(since this gives me Data!E1 and Data!E2.  Instead, I want C1 to equal the next column in the raw data (Data!D1 and Data!D2)

If there any way to prevent or override the jump of 2?

I have also tried using R1C1 referencing, but, in this case it won't increment...

cutting and pasting Data!RC3:R[2]C3  from A1 to C1 results in no change.  There is no auto incrementing, and C1 still references RC3.

So, I am stuck with either incrementing by 2, or, not incrementing at all!!

How can I either a) increment by only 1 using the A1 style nomentclature, or b) force the r1c1 style to increment??

I have even tried using various functions such as OFFSET, INDIRECT, and ADDRESS.

Is there a way to paste a formula into every other column, which only moves the reference in the raw data 1 column, not 2???

Answer 1

Thank you for your response, however, neither one captured the essence of what I am trying to do.

The first solution offered would possibly work for just  header, but not for the data underneath.

The second does not make sense for 2 reasons.  1) I am not copying a formula, only a reference. 2)  You suggested solution would not modify the reference at all, I DO want it to increment by 1, but as stated in the problem I do not want it to increment by the default of 2. (changing Data!C1 to Data!E1)

Thanks anyway.

Answer 2

See these links.

http://www.mrexcel.com/forum/excel-questions/65004-formula-copy-row-data-into-column-data.html

http://www.techrepublic.com/article/copy-an-excel-formula-without-changing-its-cell-references/