[Solved] How do you copy an array formula down a colum?

Discuss the spreadsheet application

[Solved] How do you copy an array formula down a colum?

Postby yanowamsayin » Mon Aug 25, 2014 11:42 am

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.
Last edited by yanowamsayin on Mon Aug 25, 2014 12:49 pm, edited 1 time in total.
yanowamsayin
 
Posts: 1
Joined: Mon Aug 25, 2014 11:40 am

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

Postby Lupp » Mon Aug 25, 2014 12:16 pm

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 formulae in more than on columns simultaneously.
On Windows 10: LibreOffice 5.4.4 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1793
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Villeroy » Mon Aug 25, 2014 12:31 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 31 guests