Ask Your Question
0

Calc data named range changes - bug or me? [closed]

asked 2014-06-26 06:48:00 +0200

dave_b gravatar image

updated 2015-09-02 22:39:01 +0200

Alex Kemp gravatar image

In LO 4.2.4.2 Calc, there is the possibility of named data ranges. Selection and use seems quite simple (Data->Define range/Select range) and unambiguous. However when I do this and for example run a filter, the range limits change spontaneously. This not only screws up the filter, but modifies the range shown for the name. Is there some setting which says "magically override my selection because calc has a better idea", or is this a bug?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-27 10:45:55.066052

Comments

You have to first select the data range and then define the filter. The filter is associated with the data range. Please give a step by step description about what you have done, so that it is reproducible.

Regina gravatar imageRegina ( 2014-06-26 10:30:57 +0200 )edit

Select a range with names in top row, in this case A5:AK59. Then Data->Define range and give a name ("Contacts") - the range then appears with all absolute references ($ signs). Do something else, then Data->Select range, and correct range is highlighted. Then select a filter (in this case column name such-and-such non-blank - this works. Then Data->Filter->Reset filter and bingo, the named range, complete with absolute references, becomes $A$5:$AK$65. The only odd thing is that the last used row is row 65 - there are some cells used below the table, with references inside the table in quite normal formulae.

dave_b gravatar imagedave_b ( 2014-06-26 13:58:59 +0200 )edit

Please examine: When you select the data range via Data > Select and then choose item Filter > Standard Filter, does the selected range expand to the immediately following not empty rows? I notice that here. I find no option to disable the auto-expanding. Conclusion: Use at least one empty row after your data range.

Regina gravatar imageRegina ( 2014-06-27 12:06:32 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2014-06-26 09:57:03 +0200

Lupp gravatar image

updated 2014-06-26 10:05:12 +0200

The "named range" actually is acting as a "named reference" or even as a "named expression". You have to regard the rules of absolute versus relative addressing / referencing.

(I cannot say anything about experiences with filters so far.)

edit flag offensive delete link more
0

answered 2014-07-01 05:27:00 +0200

dave_b gravatar image

Thanks Regina, that's the trick for this bug. While I had some empty rows at the end of and inside the data range, that's not it - you need at least one empty row immediately after the last row in the range. That row is of course where you often have things like totals - so insert a blank row.

edit flag offensive delete link more

Comments

But that's still a bug or misbehaviour since the Calc ignores all the empty rows at the end of the range and reduces the range height by the number of empty rows (probably does the same for the range width, have not tested). Calc should respect the fixed absolute named range given by user (as OO Calc does).

AA70 gravatar imageAA70 ( 2016-09-06 01:05:17 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-06-26 06:48:00 +0200

Seen: 538 times

Last updated: Jul 01 '14