How do you copy an array formula down a column in Calc?

[rsmith284]

I have the following array formula in cell B2:

Code: Select all

=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C2)*($N$2:$N$110=E2);0);3)

and I want to copy it down a column, so I want:

Code: Select all

B2 {=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C2)*($N$2:$N$110=E2);0);3)}
B3 {=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C3)*($N$2:$N$110=E3);0);3)}
B4 {=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C4)*($N$2:$N$110=E4);0);3)}

etc

I enter the formula into B2, I press CTRL+SHIFT+ENTER, then I copy the formula down the B column. This only gives me:

Code: Select all

=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C2)*($N$2:$N$110=E2);0);3)

The relative reference to C2 and E2 seems to be treated as an absolute reference. I want E2 and C2 to change.

I'm using OpenOffice Calc, so I have ; instead of , in the functions.

[acknak]

Greetings and welcome to the community forum!

... I copy the formula down the B column.

If you're using the drag-fill handle to do this, it doesn't work with array formulas; I don't know why. You can instead copy the cell with the array formula, select the cells you want to copy it to, and paste.