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!

  • 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

(You need not type. Just select the relative cell of Worksheet Tab1)

Copy it down the rows to get the desired output.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.