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. 
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.
I don’t know if I understand your question.
But to fill row 6 with data and formulas of row 5.
Select row 6, click on his row number.
[Ctrl+D] copies in row 6 with data/formulas of row 5.
The question is: how to have an area on a spreadsheet expand, and populate formulas and values, and cause named ranges referencing the area to expand.
Using ctrl-d / fill handle populates formulas but does not update the named range.
Inserting rows updates the named range but does not populate formulas.
Following your suggestion will populate the formulas but the named range reference will remain stuck at Row 5, and not expand to include Row 6.
With Menu/Tools/Options/LibreOffice Calc/General - Expand references when new columns/rows are added, actives.
Select the row following last data.
[Ctrl++] insert row (row remains selected)
[Ctrl+D] insert data/formulas
It is not one step but it’s quick.
Selecting the row after the last data and then operations does not cause the named range to expand.
Inserting a row inside the named range does cause it to expand, but that puts a blank row in the middle of the data.
Using ctrl-d or the fill handle on the last row properly fills in the formulas in the new rows below, but does not expand the named range reference.
I tested it before reporting, have you set up the option mentioned in the comment?
I tested before replying. 
No, I did not have the Menu > Tools > Options > LibreOffice Calc > General > Expand references when new columns/rows are added option turned on. Turning it on enables my case to work. I believe I had tested both ways ~2 years ago and neither worked for me, but I don’t recall for sure now.
It’s still a multi-step process, but I’m intending to automate it anyway so that shouldn’t be a hindrance.
Thanks mariosv!
Edit: Tested just now on
Version: 6.4.7.2
Build ID: 1:6.4.7-0ubuntu0.20.04.6
It would make more sense to put tickets in column numbers with a conditional formula. I am going to code without testing, so there
will be errors, likely around comparing “” to a number.
Lets define the columns.
To make it easier, nobody gets more than 10 tickets.
An=Num_tickets for this person,
Bn = Blank, just for spacing
Cn…Mn = numbered tickets
Now let’s formulate cells. Start with row 1
C1: =if( A1 > 0; 1; ‘’ )
D1: =if( C1< A1; C1+1; ‘’ )
Then drag A1 across to M1
C2: if( B1>0; 1+MAX( C1: M1 ); ‘’ )
D2: : =if( C2 < A2; C2+1; ‘’ )
Then drag A2 across to M2
From there, drag the whole block down.
Columns C1 to M-max will have sequentially numbered
tickets.