Dynamic named range

[ClaudioR]

hi, i need a tip…

I download from a DB a table whose number of columns is fixed, but obviously the number of rows varies with each update; I suppose they are compiled up to column J.
Then I define an area, with the command define name, coinciding with that of the data which will therefore have a name and an expression of this type that defines the interval

$Sheet1.$A$1:$J$5

5 lines of data (5 records); if I update and the rows became 7, then the new interval I would need to evaluate would be

$Sheet1.$A$1:$J$7

but if it remains defined as above I take I do not consider two lines.

Is it possible to ensure that the formula with which the interval is defined can vary automatically and consequently adapt to the length of the list?

In excel this: https://www.ablebits.com/office-addins-blog/2017/07/19/excel-dynamic-named-range/

This is the question.

Thanks

[Lupp]

Do you think “dynamic named range” is a well dfined term, please tell where I can find the definition.

[ClaudioR]

you are right, I will correct the question

[Lupp]

I can’t compare to Excel.
However, range references can be based on formulas working with COUNT() or COUNTA() e.g.
In most cases OFFSET() is the function best suitable for the pupose.
OFFSET($A$2; 0; 0; $Z$1;1) e.g. will give a range of column A with as many rows as are calculated in cell Z1.
The output of any array formulas to cells, however, locks a range when the formula is calculated the first time, and can’t adapt to variable size.

[Lupp]

Generally there is no clear distinction between named ranges and named expressions in Calc.
Since the names of named ranges are used as references in Calc, and references can be created by expressions using INDIRECT() or OFFSET() based on arguments which are allowed to be expressions themselves, you can name such an expression to get something what you may call a dynamic named range.
Be careful. Such very special constructs may frustrate your expectations, and cause lots of problems. The maintenance of sheets using such constructs may get difficult.

>Sheet>Named Ranges and Expressions>Define (probably >Manage)