Is there an equivalent of a range formula in Calc

I’d like to set up a dynamic named range in Calc, which has multiple rows and columns, and would automatically increase in size if let’s say “column D” would have more values added.

I have tried various methods, among them the following one, which worked as a function in a cell, but would not budge as a criteria in the named range expression:

CONCAT("$Validity.$B$1:" & ADDRESS(COUNTA($D:$D),COUNTA(1:1)+1))

Any help or suggestions would be greatly appreciated.

The following syntaxes (when applied to the “Range or formula expression:” in “Manage Names” window do not work. I tried them both already before even posting the question:

INDIRECT($Sheet1.$A$1:$C$3) or OFFSET($Sheet1.$A$1,0,0,3,3)

If anyone has any other suggestions, I’d be very grateful.

INDIRECT(your formula here) is one common way to go about this. The function interprets text data (character string) and returns a reference.

I usually prefer OFFSET(), which also returns a reference.

Click the function names above to see the help page for each.

A frequently applied proceeding is to not “add values” but rows to a data range. New data are then entered into cells of the new rows. In some cases columns may replace rows insofar.
Anyway: There is the option Expand references when new columns/rows are inserted under >Options>LibreOffice Calc>General.
Having this options enabled, the described proceeding will make your references (and named ranges) adapt as you expect it.

The variant is to not enable the mentioned option (if this has unwanted side-eggects), but to keep an empty row/column at the edge of my range and included by references, and to insert new rows/columns between the filled part and the empty edge before I enter new data.

Just tried. Didn’t work. At least not in the intended manner. Either way, thank you for the suggestion.

Strange. Since I didn’t use the mentioned option recently, I also tried again (with V7.0.4.2) and it worked as I had described it. May there be a misunderstanding?
The mentioned option enabled now!
Let’s assume there is a named range (global scope) myRange defined to mean $Sheet1.$A$5:$A$499. Before entering a new value into A500 you already went to A499, and inserted a new row below. Use the named range manager an find that the referenc myRange stands for was changed to $Sheet1.$A$5:$A$500. That’s what I meant.

Before entering a new value into A500 you already went to A499, and inserted a new row below.

Inserting rows like that doesn’t populate any formulas in the table into the new row, so it’s highly non-useful.

Grabbing the fill handle and pulling down does what most people want, and, the ‘expand’ option does not expand references to include the new rows. :frowning:

Surely, it does.
Enter =SUM(A1:A2) somewhere.
Insert anywhere between A1:A3.
The formula changes to =SUM(A1:A3)
It expands all references in formulas, names, charts, conditional formatting, form controls, validation and elsewhere.
InsertCalcRows_LO.odt (23.3 KB)
is a macro which takes care of option “expand references” and drags down adjacent formulas.
The best solution to all these problems would be a database, of course.

Insert anywhere between A1:A3.

I specifically said my example does not do inserts because it is not useful because inserting does not populate the inserted rows with formulas (you manually have to do that). Instead, my example is to do the useful thing which is to highlight the last row and use the fill handle to copy new rows down. In that instance, the ‘expand’ option does not update references to account for the new rows.

It does work when you insert, you are correct about that. It does not when using the fill handle.