Define a Named Range as an array

[rocinante]

Hello, new to LibreOffice Calc.

I’m trying to convert some of my Excel sheets over the LibreOffice and having an issue Defining a Named Range as an array.

Here’s the formula I’m using. I notice it returns #VALUE! by default

=OFFSET($'Chart Backup'.$B$61,0,0,$'Chart Backup'.$G$18,1)

I figured out that I can make it output as an Array in the Function Wizard and it displays the function like this:

{=OFFSET($'Chart Backup'.$B$61,0,0,$'Chart Backup'.$G$18,1)}

I tried using this formula in the Name Manager, but it doesn’t seem to be working.

Is there anyway I can get this function saved to the name manager? The formula outputs correctly on its own, but it doesn’t work in the Name Manager.

Forgot to add…
Version: 24.2.4.2 (X86_64) / LibreOffice Community
Build ID: 51a6219feb6075d9a4c46691dcfe0cd9c4fff3c2
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

[dscheikey]

You can enter the formula normally without curly brackets in the name manager. If you then want to display all cells, you must use a matrix formula here.
In my example, I have given the formula the name ‘ABC’. To use the cells, you must use =ABC as a matrix, i.e. {=ABC}. If the result is displayed in a cell, for example =TEXTJOIN(", ",1,ABC) then there is no need for a matrix formula.

rociante.ods (10,5 KB)

I hope this has helped you.