How do you copy an array formula down a column ?

General discussion on OpenOffice Calc and its spreadsheets.

How do you copy an array formula down a column ?

Postby Ricky G. Marion » Sat Sep 13, 2014 10:46 am

Hi.

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)}

in each of the cells I copied to.

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


Thanks in Advance,
Ricky G. Marion
 
Posts: 25
Joined: Mon Mar 24, 2014 6:33 am

Re: How do you copy an array formula down a column ?

Postby Champ_AN » Mon Sep 15, 2014 6:19 am

Hello,

Please follow the instruction given below:
Use 'Edit' > 'Fill' > 'Down' (default shortcut: Ctrl+D) after selecting the range to fill. You may also press Ctrl in addition to dragging the "little square". This is NOT necessary if you are filling down by dragging the formula in more than on columns simultaneously.

Thanks and i hope this will help you..
Champ_AN
 
Posts: 19
Joined: Tue Mar 25, 2014 6:34 am

Re: How do you copy an array formula down a column ?

Postby Joseph S. Klein » Mon Sep 15, 2014 7:38 am

Hi,

Please follow the instruction given below:
1) I can copy the source cell (Ctrl+C), select the target range and paste.
2) I can double-click the cell handle and the formula drags down along the adjacent used cells in column A.
3) menu:Edit>Fill>Down... on the selection with the formula on top.
4) Ctrl+Drag&Drop

Only the drag&drop expands the same array over multiple cells which is not what you want in this case.

Hope this would be helpful.
Thanks,
Joseph S. Klein
 
Posts: 38
Joined: Mon Mar 24, 2014 6:28 am


Return to OpenOffice.org Calc

Who is online

Users browsing this forum: No registered users and 0 guests

cron