COPYING FORMULAE

[Frankie]

Hi,
I come across small problem that I can't copy formulae for relative address
reference to move every 4 cells downwards.
For instance, I have a table as follows :
A B C
CODE PROD_A PROD_B
1 AA 100 150
2 AB 200 2000
3 AC 300 600

Formulas relative to the table to be copied :
+A1 +B1
+A1 -B1
+A1 +C1
+A1 -C1
After copy formulas, I want following ref. so on and so forth :

+A2 +B2
+A2 -B2
+A2 +C2
+A2 -C2

How can I do that without my own manipulations ? I tried hilited the area
but the formulas jump 4 cells forward.

Much obliged if a simple solution can be given.

Rgds,
Frankie

 

[Roger Govier]

Hi Frankie

Assuming you are copying the data to other columns. I used columns F and G
In cell F1
=INDEX(A:A,INT(ROW(A4)/4))
and copy down as far as required
In G1 =INDEX(B:B,INT(ROW(B4)/4))
in G2 =INDEX(B:B,INT(ROW(B4)/4))*-1
in G3 =INDEX(C:C,INT(ROW(B4)/4))
in G4 =INDEX(C:C,INT(ROW(B4)/4))*-1

Highlight cells G1:G4 and use the fill handle to copy down as far as
required.
The fill handle is the small black solid cross that appears when you hover
over the bottom right corner of your highlighted range