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

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 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

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.

( 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.

( 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.

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

Sort by » oldest newest most voted

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.)

more

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.

more