Apache OpenOffice (AOO) Bugzilla – Issue 25769
Non-contiguous selections don't transport to the 'Define Names' dialog
Last modified: 2013-02-07 22:35:09 UTC
Non-contiguous selections don't transport to the 'Define Names' dialog (Insert->Names->Define). Open a new spreadsheet and rename 'Sheet1' to 'Test1'. Select cells B2:D3; hold down the Ctrl key and select, in turn, cells A1 and A4. With these cells still selected, open the Define Names dialog. The 'Assigned to' box will not show the complete specification for the selection; it will only show the specification for the last-selected cell, A5 ($Test1.$A$5). Enter the name 'TestRange' and select 'Add' and 'OK'. The formula bar drop-down box shows 'TestRange', but selecting it activates a selection for cell A5 only. The expected result is that the non-contiguous range specification would appear in the 'Assigned to' box and that entering a name would define a named range for that specification. Re-select range B4:D5 (a contiguous selection). Open the Define Names dialog. The 'Assigned to' box *does* show the complete specification for the selection ($Test1.$B$4:$Test1.$D$5). Enter the name 'Block' and select 'Add' and 'OK'. The formula bar drop-down box shows 'Block', and selecting it activates its selection correctly. This shows that single or contiguous cells' specifications are transported to the Define Names dialog, that they can be defined as named ranges directly, and that this does not work for non-contiguous cells' named ranges. Making defining named ranges for selections of non-contiguous cells behave as happens for single and contiguous cells would be a good enhancement for consistency and productivity. This is the default behaviour for Excel, for example. BTW, the same named range for 'Matrix' in an Excel spreadsheet will be imported correctly into Calc. Presently, the only way of defining a non-contiguous named range is to enter the range's specification into the 'Assigned to' box manually. This is time-consuming and very prone to transcription errors.
Hi Bettina, one for you. Frank
I believe that up to now Calc has been unable to specify unions between ranges, even by entering the range's specification into the 'Assigned to' box manually. There hasn't been a union operator. However ODFF requires this and it seems to be targetted for OOo3.0 under issue 32341, with the work having already been done. The new union operator would appear to be ~, as ODFF. This issue and issues 25770, 25771 could therefore disappear. Just trying to tie things together; hope that's helpful.
@drking: You're mixing things up. Calc doesn't allow a multi-selection to be defined as a name, one can manually define two ranges separated by a semicolon though, which works for functions accepting multiple parameters with range reference arguments, such as SUM(). The implementation of the union operator per issue 32341 doesn't change that a multi-selection isn't accepted by the Names Define dialog.
> Calc doesn't allow a multi-selection to be defined as a name Yes, I know, but the OP said: > Presently, the only way of defining a non-contiguous named range is to enter the range's specification into the 'Assigned to' box manually. which is wrong, isn't it? There isn't a way within Calc. Actually a non-contiguous named range imported from Excel does seem to work, more or less. eg AREAS(mynamedrangefromexcel)>1 --- > The implementation of the union operator per issue 32341 doesn't change that a multi-selection isn't accepted by the Names Define dialog. That's a shame. I was hoping to reduce the number of outstanding issues.
@drking: > > Calc doesn't allow a multi-selection to be defined as a name > Yes, I know, but the OP said: > > Presently, the only way of defining a non-contiguous named range is > > to enter the range's specification into the 'Assigned to' box > > manually. > which is wrong, isn't it? There isn't a way within Calc. There is. You have to type the sequence, selecting it with mouse is not possible. It depends though how the defined name is used: > Actually a non-contiguous named range imported from Excel does seem to work, > more or less. eg AREAS(mynamedrangefromexcel)>1 INDEX() and AREAS() are two special cases in the sense that they do accept an (area1;area2) argument as reference parameter. A defined name to use with that needs to include the parentheses in its definition, same as in Excel. Functions like SUM() currently do not support multiple ranges as one parameter, therefor the name definition must not include the parentheses, and the sequence of ranges defined is passed to the function as a sequence of parameters, not one parameter with multiple ranges. > > The implementation of the union operator per issue 32341 doesn't > > change that a multi-selection isn't accepted by the Names Define > > dialog. > That's a shame. I was hoping to reduce the number of outstanding issues. Implementation of the operator is calculation engine core and not related to UI mouse selection in dialogs.
To grep the issues easier via "requirements" I put the issues currently lying on my owner to the owner "requirements".