CODEWITHSUNDEEP
openoffice-calc
Irina
Irina

Reputation: 21

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

I have the following array formula in cell B2:

=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:

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:

=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.

Upvotes: 1

Views: 31521

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

In Calc (OpenOffice or LibreOffice) you will always create one array formula for the whole overlined area if you fill down or sidewards a array formula with the fill handle (small black dot in the bottom right corner of the active cell).

If you need to create single array formulas for each cell, then you have to copy the formula, then select the destination cells and paste the formula.

You can also copy, not fill, if you hold Ctrl + Shift while dragging the fill handler.

Upvotes: 1

Related Questions