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