Auto-expanding single row range doesn't work

English
, ,
#1

My data range (w/o column labels) initially includes 1 record, then new records are added to it.
The option “Expand references when new columns/rows are inserted” is set.
However, range expansion when adding columns/rows occurs only if there are more than 1 columns/rows respectively in the named range (2 or more).
Is this intentional behavior?

UPDATED:
Now, when adding a new record, if this is the second record, then you also need to adjust the range. If this is not done, then all subsequent additions will pass by, and the named range will remain a single record.
I wonder who needed it and why?

Version: 7.2.3.2 / LibreOffice Community
Build ID: a49ed84f3d037188bbbcb324f9afc3796d887539
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3
Locale: ru-RU (ru_RU.UTF-8); UI: en-US
Calc: threaded

#2

t71251.ods (12.6 KB)
Works for me.

With a true database this would be a non-issue.

#3

The answer could not be accepted. It was about a named range of one row (no headers).

#4

Still works for me.
t71251_2.ods (13.5 KB)

#5

I do not get expansion (with option set) when adding a row above or below a single row that is a named range. Behavior is a little different if that row is the first row, but in general it looks like there is no expansion, and the named-range dropbox in the toolbar misrepresents the current selection right after the insertion. (7.2.2.2)

Villeroy, can you double check that your Named range is really what the named-range dropbox is indicating right after the insertion? Perhaps the not-real-time “Named” showing/not showing in the dropbox is throwing off the OP with respect to their expectations.

I seem to remember working with range extension in Excel 2007 and developing macros for line insertion to mitigate not-on-last-row behavior. So I don’t have the same expectations as the OP as far as correctness goes, but I do think the name-range dropbox can “lie” right after the insertion operation.

1 Like
#6

Reset your user profile.

No, better use a database, even for the most trivial list keeping.

#7

Rebuilt the spreadsheet in Safe Mode; when I insert a row above, then right after insertion the named-range dropbox shows Named even though Named is still only the row below the selection. If I repick Named in the dropbox it moves correctly to the still-one-row range Named.

#8

Only for formatting, not for named range. Version 7.1.7.2.

#9

Two rows at least are required. Otherwise a formula like =SUM(row) would become =SUM(2rows) which nobody wants.

This is a non-issue in a database, even when you add the very first record to a table.

#10

I don’t understand this.

#11

Open my document.
E1: =SUM(A1:D1)
Insert at A2:D2. The formula in E1 remains the same which is want you always want when you sum up a vector (a single row or column).

Now the same with a 2-dimensional range:
E1: =SUM(A1:D2)
Insert at A3:D3. The formula in E1 changes to SUM(A1:D3) expanding the 2-dimensional range from 2 to 3 rows.

#12

@Villeroy, when we use a database range, we always include the header row. But we usually exclude the header row when creating a named data range. Remember when auto-creating ranges: labels on the left and top, and ranges (rows/columns) inside (labels here are range names). “Usually” because using an advanced filter, for example, requires column (field) headers.

And further. What problem am I solving? I created and automated everything in my application. Then I deleted all the data and started testing when adding the first record, etc. Problems started…
The range does not expand.
The COUNTIFS formula expects a range reference, and with one record, if an argument returns not a vector, but a scalar, then the formula doesn’t work. It is necessary to enclose the value in curly braces if one record is, e.g.: COUNTIFS(…, …, IF(ROWS(Data)=1;{1};ROW(Data));">0"). A lot of interesting things creep out…
What’s going on with functions requires a separate post.

#13

But you are still mis-using a spreadsheet as a cheap database surrogate. This bird won’t fly, no matter how much Basic code you throw at the problem.

Sorry, it still works for me. I start the named range with 1 “record” plus header or 2 “records” without header and the named range expands just like the database range.
It must not work with a single row of cells because this would destroy row based calculations. As soon as the reference is 2-dimensional with columns and more than one row, all references expand. Named references, db-ranges, references in formulas, conditional formattings, validations, charts, form controls.
[Edit: this is what I found out with OpenOffice Calc years ago. Meanwhile I do not use Calc for anything but calculation models and database reporting]

#14
  1. Behavior for insertion follows Villeroy’s comments for me on 7.2.2.2. Insertion always expands for any choice of Insert Above/Below for any row (including first or last) for any named range of two or more rows.
  2. There is no expansion for named ranges of a single row.
  3. If you do the following, the named-range drop-box will incorrectly still show that the entire named range is selected directly after insertion, even though the selection is still only on the original named range:
    a) Create a two-row named range named MyName.
    b) Select the MyName named range in the named-range drop-box.
    c) Right click on one of the highlighted row stubs and select Insert Below
    d) A two-row insertion will be made. The named range will be expanded. However, the named-range dialog will still show “MyName” even though only the first two lines of the now four-line named range are highlighted.
    e) Reselecting “MyName” in the named range drop-box will change the selection to the full, expanded named range.

This final behavior is not especially evil, but it could mislead users into thinking that only the first two lines are still in the named range and that expansion has not occurred.

1 Like
#15

This topic was not useless. So the range should be two-dimensional, as @Villeroy said (2 or more rows, 2 or more columns). This requires you to write additional code to work with range references when you start data entry. Or, as @Villeroy said, entering data into Calc is a bad idea: you just need to analyze the prepared data.

#16

Referred to the Calc Guide v. 7.1 (p. 477):

Expand references when new columns/rows are inserted specifies whether to expand references when inserting columns or rows adjacent to the reference range. This is only possible if the reference range, where the column or row is inserted, originally spanned at least two cells in the desired direction.

This is the answer. And there is a small inconvenience in this: when adding a second record, do not forget to expand the range yourself. To work with worksheet formulas, we don’t need to include headers in the range. But one record range doesn’t expand.

Automating the process of adding a record, you should add an Add New button to the sheet, which will correctly handle the situation with one record.
And an inexperienced user should be warned that it is impossible to simply fill in a new row below, you must click on the Add New (record) button. In this case, a new record is inserted, and then the range is expanded automatically. And if this is the second record, then the reference to the named range is also adjusted.

I agree that these are too many “ifs” to get started with spreadsheets.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.