# Excel: preventing incrementing references when copying group of data

I have a pretty hard Excel question I'm seeking a solution for. I have one Excel file with two tabs.

• Tab1: raw data
• Tab2: raw data processed and grouped for a nice lay-out for printing purposes

Contents Tab1:

``````A1 = 1
A2 = 2
A3 = 3
A4 = 4
A5 = 5
``````

Contents Tab2:

``````A1 = Result
A2 ='TAB1'!A1 (a formula with reference to A1 in Tab1)
``````

Wen I'm selecting A1 and A2 in Tab2 to drag/copy I get the following result:

``````A1 = Result
A2 = 1
A3 = Result
A4 = 3
A5 = Result
A6 = 5
``````

The problem: when I'm selecting A1 and A2 and drag/copy, the formula thinks he has to increment with 2. There has to be an increment by 1! The desired result is:

``````A1 = Resultaat
A2 = 1
A3 = Resultaat
A4 = 2
A5 = Resultaat
A6 = 3
A7 = Resultaat
A8 = 4
A9 = Resultaat
A10 = 5
``````

Anyone has ideas or suggestion. This is just a simpel projection. My actual Excel has 21 rows so the increment is 21 wherever this has to be 1!

• Dragging over cannot alter references by another way. Use `INDIRECT()` function to make correct reference, and `ROW()/2` to build it. – Akina Feb 12 at 11:45

Instead of using cell references (`A1` `A2` etc.), enter one more column in `Tab1` on the left and enter the data in rows as `A1`, `A2`, `A3`, `A4`, `A5`. In the second column enter the results: `1`, `2`, `3`, `4`, `5` etc. When you drag `A1` and `A2` together down the rows you do get `A3`, `A4` etc. So you can enter as many as you want.
Now use '&' to concatenate the strings in cell `A1` of `Tab2` as below:
`="Result Of "&'Tab1'!A1&'Tab1'!B1`