Copying an indirect function, making it dynamic

[Lucca]

asklibreofficehowtocopyindirectfunction.ods (15.9 KB)

Hi,

in attached file you’ll find 3 sheets (Blad3 doesnt matter (empty). In the first sheet ‘Blad1’ in cells A3:D3 I’ve found (after some searching on Youtube ) formulas to copy some cells from sheet 'Ploeg1). NOTE that the aim was to use cell A1 of ‘Blad1’ to make the formulas…

I didn’t succeed to refer to a column in the sheet ‘Ploeg1’ which would have been even more efficient.

But the main question overhere is:
You notice that I’ve dropped down the formulas from a3:d3 to A4:D7 and they do not adapt to the rows in Ploeg1. I know an INDIRECT function ‘indirects’ textes to cell references and therefore I somehow understand that those formulas in A3:D3 do not adapt to the rows…

How can I write my formula differently so that they do. I’ve tried several ways, for instance =INDIRECT("$"&$A$1&").d2 but libreoffice doesn’t read my intentions on that

Furthermore, I would be pleased if it would also be possible to make the rows dynamic/adaptable.

Thanks again for your concerns,

[ady]

I must be missing something, because I do not really understand the intention when you use INDIRECT() instead of referring to the cells directly.

For example, in cell “$Blad1.B3”, you could introduce the formula =$Ploeg1.G2 and then copy the cell to the rest of the table.

The simple formula points to the relative location of the table in the worksheet named “Ploeg1”.

As I said, I must be missing something about the real goal of the question.

[Lucca]

Right. I forgot to mention that the reason is that I want to automate this operation for 14 other sheets (Ploeg1 until Ploeg14) and furthermore in the sourcefile ‘Ploegx’ an autofilter can change values all the time… So, by using these formulas, I want to automate and adaption to the 14 Ploeg’s and the filtering. These sheets will change from day to day, if I need to adapt all formulas manually this would take me time that LibreOffice can grant me. Thanks Ady.

[Lucca]

furthermore there are 20 rows to copy down. So, it would spare me a lot of time.
But of course other functions are welcome. Thanks

[ady]

You might be interested in functions such as ADDRESS, INDIRECT, OFFSET, INDEX.

There is a recent topic that has similarities (although not exactly the same use-case); see [Allow User To Input Cell Reference].

[Lucca]

another approach would have been using the options Height and Width of the Offset formula to fill out the whole array A3:D7 at once, but…

1. I think Libreoffice version 24.8 is not ready to build the arrays (it seems that Excel at date can build arrays out of the offset function using the Height and Width options)
2. I think columns of the sourcefile should then been organised equally. Here columns D G H and I has to come in A B C D
   Experts out here should be able to do so, my knowledge however shortens.

[Lucca]

thanks @ady for your suggestion.

I managed to make that INDIRECT dynamic (able to copy down) by putting it this way:

=OFFSET(INDIRECT("$"&$A$1&".d1");ROW()-2;0)

Still have to manually change the column number, but this has to be only once (before copying the formulas for the 13 other Ploeg’s).

So, thanks for your idea.

[karolus]

@Lucca

INDIRECT("$"&

literal $-signs are complete nonsense in INDIRECT-context!

=OFFSET(INDIRECT($A$1&".d1") ; ROW()-2 ; 0 )

[ady]

@Lucca

Still have to manually change the column number

Why exactly? The COLUMN() function (optionally using a cell reference from another worksheet of the same workbook) could help, instead of using some constant value (of zero or any other constant) for the initial column reference argument for the OFFSET() function.