Using IF function, how to write several rows

[carles99]

Hi,
that’s what I need to do:

If I write a particular name in a cell, for example “John”, I would like that libreoffice calc writes automatically in a particular column some other names one below the other in the same column like:

hello
here
I
am

Is it possible?

Thanks a lot!!!

(Editing by @Lupp regarding the “second question” posted as a fake answer, writing as if @carles99 did:)

Thank you very much, @librebel, it worked!
However, my original intention was to learn if and how it is posible to call from the array data written in certain cells? Like: =IF($A$1="John";{C1;C2;C3;C4};{;;;}) or like =IF($A$1="John";{C1:C4};{;;;})

I tried it and it doesn’t work. Thanks in advance!

[librebel]

Hello @carles99,

suppose that the cell to check for the value “John”, is cell A1.

then you could write a formula in cell B1 as follows:

=IF($A$1="John";{"hello";"here";"i";"am"};{;;;})

NB. This is an array-formula;
after typing this formula into the Formula Bar, do not press ENTER, but press CTRL+SHIFT+ENTER instead.

---

[carles99]

Thank you very much!!! It worked!!!

If you don’t mind, I would like to ask you an other question:

Is it posible to call from the array data written in certain cells? Like:

=IF($A$1=“John”;{C1;C2;C3;C4};{;;;})

or

=IF($A$1=“John”;{C1:C4};{;;;})

I tried it and it doesn’t work.

Thanks in advance!

[erAck]

This =IF($A$1="John";C1:C4;{;;;}) works. Inline arrays (the {…}) can only have literal numeric and text values.

[Lupp]

(Answering the second/clarified question:)
No, no way … to do this in an arbitrary cell addressing a group of arbitrary cells elsewhere.
This at least if you rely on standard functions.

The paradigm of spreadsheet software is that cell contents only can be created or changed by
-1- direct editing.
-2- special tools like ‘Data’ > ‘Sort…’ or ‘Insert’ > ‘Pivot Table…’ e.g.

Formulas only are allowed to change the result assigned to the cell the formula is contained in. The result then will be displayed depening on the cell’s format properties.

Thus: What you, @carles99, want can only be achieved by an array-formula entered into the first cell of the intended output range and then ruling every single cell of this range as its formula. How to do so in any specific case is not told with a few words. You may start to learn by studying this attached example. Please note that an array-formula must be entered with Ctrl+Shift+Enter after its creation and after any editing. Only the complete range can be edited.
@librebel already answerd including parts of my answer. I wouldn’t descend that first answer. I only try to keep things together. My example demonstrates how to get the words for output from an array. Propably this was not in the center of what the OQ tried to achieve. The question was not exactly clear with this respct.

[Lupp]

Generally you should stick to this. Trying to circumvent the above mentioned restriction by user-coded functions is (within limits) possible, but against the grain. The working of such functions is not reliably specified anywhere, and thus subject to changes in the software without notice.