8

In Google Sheets, I can compose a formula as follows to easily mirror data from one sheet tab into another:

=ARRAYFORMULA(Sheet1!B1:D9)

What is the equivalent of this formula in LibreOffice Calc?

Improve this question

1 Answer 1

Reset to default
3

According to Libre Office help page:

Creating Array Formulas

If you create an array formula using the Function Wizard, you must mark the Array check box each time so that the results are returned in an array. Otherwise, only the value in the upper-left cell of the array being calculated is returned.

If you enter the array formula directly into the cell, you must use the key combination Shift+Ctrl+Enter instead of the Enter key. Only then does the formula become an array formula.

Array formulas appear in braces in LibreOffice Calc. You cannot create array formulas by manually entering the braces.

The cells in a results array are automatically protected against changes. However, you can edit or copy the array formula by selecting the entire array cell range.

Source and more about array formulas: Array Functions

So in this particular case all you need to enter is 

=$Sheet1.$B$1:$D$9

and press Shift+Ctrl+Enter

Or

Insert -> Function, write your formula and check the box saying Array

enter image description here

Improve this answer
5
  • 1
    it's also the same on Excel
    – phuclv
    Commented Jun 2, 2017 at 12:19
  • But this is not an equivalent to ArrayFormula, at least not a complete one. Consider in range A1:A3 containing three numbers, what's the equivalent of formula =SUM(ARRAYFORMULA($A$1:$A$3*$A$1:$A$3)) in Google Sheet? So far I cannot find the answer without creating an intermediate array at somewhere.... What ARRAYFORMULA can do is to eliminate that intermediate array.
    – Bing Ren
    Commented Jul 8, 2019 at 2:12
  • 1
    @BingRen If I understood this correctly, the formula you mention works in Google Sheet and you want the equivalent in Libre Calc. If this is the case the equivalent is =SUM($A$1:$A$3*$A$1:$A$3). And as suggested in the original post, if you enter the formula directly into the cell you will need the key combination Shift+Ctrl+Enter to apply it as an Array. They should produce the same results
    – Jimmy_A
    Commented Jul 8, 2019 at 10:41
  • Thanks Jimmy, your understanding is correct and your formula works identical to my example. This expanded my knowledge of how Excel array formula works. I even tried another example which combines the calculation of two arrays of different sizes and the Excel array formula still works. Now I cannot produce an example that Excel array formula cannot match.... thanks!
    – Bing Ren
    Commented Jul 10, 2019 at 1:50
  • @BingRen No worries. It is a bit frustrating that the tools require a different but also so similar syntax. In the end, the thing that changes is that in Google you need to type down ARRAYFORMULA. In Excel and Calc you just need to apply it.
    – Jimmy_A
    Commented Jul 11, 2019 at 9:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .