Make a Calc row name follow a known end column without losing values after gaps
By ON Lab · Sources checked November 8, 2026
If B2 already contains the final column number of a row range starting at C2, the required width is B2-2. A range-valued OFFSET name can use that width. Do not substitute a nonempty-cell count when the contiguous range must include gaps: our native Calc fixture lost its last item when we did that.
A reader's fixed name covered C2:I2, while an existing helper reported final column number 9. They wanted the name to extend when data reached J2 and K2 and the helper changed to 11. We tested this named-expression stage with original data, a deliberate interior blank, and controlled helper values.
Separate a column coordinate from a range width
Column C is the third column. A contiguous row range from column C through column I therefore has seven cells, not nine. In general, its width is the final column number minus the starting column number plus one.
width = final_column - 3 + 1
= final_column - 2For this example B2 must contain a valid, numeric whole-column index at or beyond C. Our tests set it to 9 and then 11. They did not reproduce the original whole-row LOOKUP helper or automatically discover the final populated column.
Define a name from that known endpoint
On a sheet named Kenmerken, we defined ReaderChoices with this expression:
OFFSET($Kenmerken.$C$2;0;0;1;$Kenmerken.$B$2-2)The start cell is C2. The two zero arguments leave that start position unchanged. The height is one row, and the final argument supplies the computed width. The absolute sheet and cell references keep the intended anchor explicit.
Enter the expression through Calc's named-range or named-expression definition interface for your version, using the appropriate formula syntax. Our native test created it through the documented XNamedRanges API with an A1 base address. The test profile used English function names and semicolon argument separators; adapt syntax to the actual interface rather than treating the displayed expression as a file path.
Check width and the last returned value
Our first row segment C2:I2 contained these values, including one intentionally empty cell:
A B [empty] D E F GWe then appended H in J2 and I in K2 and changed B2 from 9 to 11. The two checks were:
=COLUMNS(ReaderChoices)
=INDEX(ReaderChoices;1;COLUMNS(ReaderChoices))| Known final column in B2 | Named-expression width | Last value |
|---|---|---|
| 9 | 7 | G |
| 11 | 9 | I |
Both stages returned formula error code zero in native LibreOffice 26.8.2.2 on macOS 27.2 arm64. This verifies evaluation of the range-valued name as the helper changes. It does not establish a particular dropdown's graphical behavior, another platform's UI, or the original reader's complete workbook.
Why COUNTA can end the range too early
COUNTA counts occupied cells rather than the coordinate span that contains them. Our interior blank made the count six before appending data and eight afterwards, although the required contiguous widths were seven and nine.
We tested the count-based alternative directly:
=INDEX(OFFSET(C2;0;0;1;COUNTA(C2:K2));1;COUNTA(C2:K2))It returned F and then H, omitting the actual final values G and I. That was not an INDEX syntax failure; the supplied width was too short for a range that intentionally included an empty cell.
A nonempty count can be suitable for another data contract, but it is not interchangeable with a last-column coordinate. Decide whether the range should preserve interior gaps before choosing the calculation.
The helper still owns endpoint discovery
The name depends on B2. Adding values to J2 or K2 is not sufficient if B2 remains 9. Our controlled example updated the data and the helper separately, so it does not prove that an untested discovery formula will update correctly.
Verify that helper independently on a small copy: test the initial endpoint, a newly appended value and a case with an interior blank. Keep the helper outside any self-referential calculation it cannot validly evaluate. If the helper has an error, inspect that error rather than masking it with a range that merely looks plausible.
This definition fixes the start at C2 and the height at one row. It does not automatically expand to more rows or infer a different starting column.
A UNO inspection result is not the same as formula evaluation
In our native API test, calling getReferredCells on this name returned null even while COLUMNS and INDEX evaluated the name successfully. Retain those observations separately. We did not treat a missing direct cell-range object as proof that the expression itself was broken.
If automation needs a literal cell-range object, inspect the documented interface contract and validate the actual reference form it supports. The formula checks above demonstrate this range-valued expression's output; they are not a guarantee that every range-inspection API or list-control UI will expose it identically.
Validate the boundary before using the name elsewhere
- Confirm the fixed start cell and one-based endpoint convention.
- Verify that B2 supplies the expected numeric endpoint.
- Check both COLUMNS and the last value, not only one displayed result.
- Include an empty interior cell as a negative control if gaps are allowed.
- Change the endpoint and confirm that the evaluated width and final item follow it.
- Then test the actual lookup or list feature that consumes the name.
The original thread reports success after an INDIRECT suggestion. The OFFSET definition here is a separate, measured way to express the known endpoint. It does not establish that all dynamic-range techniques have the same recalculation, inspection or list-interface behavior.