Bug 122318 - Not possible to create a dynamic Named Range with a calculated reference (like with OFFSET) and use it as a Print Range, like Excel can.
Summary: Not possible to create a dynamic Named Range with a calculated reference (lik...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: Calc-DataRange
  Show dependency treegraph
Reported: 2018-12-25 10:47 UTC by Bjørn Ryan Johansen
Modified: 2020-11-03 10:33 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Note You need to log in before you can comment on or make changes to this bug.
Description Bjørn Ryan Johansen 2018-12-25 10:47:01 UTC
In Excel I can assign the code OFFSET($Sheet2.$A$1;0;0;(COUNT($Sheet2.$D:$D)+1);4) as a Named Range to define a dynamic print area, and then assign the Named Range under Print Range.

If I try to do the same in LibreOffice Calc it doesn't work; I can't see the Named Range when I choose Choose Format - Print Ranges - Edit.

When I define a named range and select the checkbox "Print range" under "Range option", I'm offered this named range when editing print ranges. However, a named range based on a calculated reference (like with OFFSET) is not offered as a print range. 

You can see a video on how they do it in Excel here: https://www.youtube.com/watch?v=8Jnt6_ef2vE

My problem is that I administer some CALC sheets that may vary between 2000 to 40000 rows, and that some other coworkers uses this sheets to print reports. If I have to print all 40000 rows it will print over 400 pages, and that's not necessary if I have only 2000 rows. I would therefore appreciate if you could do this service available in LibreOffice Calc. 

Steps to Reproduce:
1. Assign the code OFFSET($Sheet2.$A$1;0;0;(COUNT($Sheet2.$D:$D)+1);4) as an Named Range
2. Use this Named Range as a Print Range

Actual Results:
A dynamic print range

Expected Results:
Not able to use a calculation as a named range along with print rang

Reproducible: Always

User Profile Reset: No

OpenGL enabled: Yes

Additional Info:
make it possible to make a dynamic print range as stated in my description.
Comment 1 einsachtzehn 2018-12-25 16:40:28 UTC
I can confirm described behavior in
Build-ID: 10(Build:2)
CPU-Threads: 4; BS: Linux 4.12; UI-Render: Standard; VCL: gtk3_kde5; 
Gebietsschema: de-DE (de_DE.UTF-8); Calc: group threaded
Comment 2 Xisco Faulí 2019-01-16 22:14:33 UTC
@Eike, @Winfried, I thought you might be interested in this issue...
Comment 3 Jürgen Mähnß 2020-10-30 10:01:53 UTC
I can confirm the bug in Version Moreover the dynamic named range are not displayed in Pivot Table as possible input.
Comment 4 Jürgen Mähnß 2020-11-03 10:33:08 UTC
I created a dynamic named range called tt. Counting of entries works well (german: anzahl2(tt)). When I try to insert a pivot table I'm offered the named range as input but then there are no fields and inspecting the source area reveals a1 and nothing else.