Issue 97655

Summary: Dropdown list shows wrong data after setting validity Criteria Allow as cell range
Product: Calc Reporter: amy2008 <amy2008>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues, jbf.faure, ooo.redflag, peter.junge, rb.henschel
Version: OOo 2.4.1Keywords: oooqa
Target Milestone: ---   
Hardware: PC   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Issue Depends on:    
Issue Blocks: 84292    
Attachments:
Description Flags
97655
none
The file corresponding to my test cases list above none

Description amy2008 2008-12-30 07:28:39 UTC
Can reproduce it with DEV300m37 on WinXP and Fedora

How to reproduce it
1 Open a new Calc document
2 Enter characters in some cells like A1:B8
3 Select range from E1 to G10(but not from G10 to E1) and set backcolor
  as Pale green, then Data - Validity - Crtieria - Allow: Cell range, Source:
  A1:B8 - OK
  Pay attention to dropdown list of each cell in cell range E1:G10
4 Select range from G30 to E20(but not from E20 to G30) and set backcolor
  as Pale yellow, then Data - Validity - Crtieria - Allow: Cell range, Source:
  A1:B8 - OK
  Pay attention to dropdown list of each cell in cell range E20:G30

Result
Dropdown list shows wrong data

Expectation
Dropdown list shows right data

Regards
Li Meiying
Comment 1 amy2008 2008-12-30 07:35:18 UTC
Created attachment 59071 [details]
97655
Comment 2 amy2008 2008-12-30 07:37:41 UTC
Can reproduce it with OOo2.4.1
Comment 3 zaguskin2 2009-03-31 12:31:14 UTC
I successfully replicated the bug in Open Office 2.4.1 under Windows XP
Professional running service pack 2. I followed the steps provided by the
original reporter, and it appears that the validity function under tools does
not correctly apply to an area of cells selected. If you set the validity of a
single cell to a range of cells, it works fine, but when selecting an area of
cells to apply validity to, it seems to offset every cell except the last one
that was in the selection process. 
 
As you move through each cell, the drop down list shifts upwards or downwards by
one spot every time. If your last selected cell is in the top left corner of the
selected area, the list seems to scroll up, meaning the first value is initially
seen, the last value is in the last spot of course, but selecting the next cell,
the initial value is no longer seen, and the last value is now in the second
last spot. This pattern holds as you move through each cell one by one. Now if
you drag from top left to bottom right to select cells, meaning your last
selected cell is in the bottom right corner, the bottom right cell of the
selection shows the values fine, but every other cell shows “REF#â€. 
 
The blue coloured area in my posted file was selected from top left to bottom
right, and referenced the cell range of A1 to B8. The bottom right cell shows
the correct values, but the first cells above or to the left of this cell show
“REF#â€. The green area was selected from bottom right to top left and also
referenced A1 to B8. The top left cell shows the correct values, but as you move
down, they begin to shift with the validated area, and eventually, cell C6 shows
up in the list. 
 
I suspect  that as you move through each cell that is validated, when you change
the selected cell within the selected range, the area of validation also shifts
relative to the cell you are currently on. So the reason why some cells would
show “REF#†is because when your range includes A1 as one of the points, the
cell tries to read left or above this cell, which is not defined, hence the
“REF#†value, which I interpret as an error or undefined. To prove this, what I
did for my final test is instead of selecting cells A1 to B4 as my range, I
instead selected B4 to C6 as my range of validity. The selected area I used is
the red one, and I highlight from top left to bottom right so my last selected
cell is on the bottom right of the red area. So as I move up, I should not see
any “REF#†references, since the top left of B4 is defined as a proper cell. As
I move up, I would expect to eventually see cell A1 in this selection. After
testing, this proved to be true as seen in the screenshot provided. 
 
If you wanted each cell to have the same validation, you would have to go
through each cell one by and set them manually. 
Comment 4 zaguskin2 2009-03-31 12:33:27 UTC
Created attachment 61313 [details]
The file corresponding to my test cases list above
Comment 5 Regina Henschel 2009-03-31 15:38:55 UTC
I cannot see any error. If you enter a range A1:B8 then it is a relative
address. When you enter it while the cell cursor is set to cell G10 it means
"row-9 column-6". The same information is written to the cell E1, but there
doesn't exist any cells at the position "row-9 column-6". That is the way
relative addresses work.

If you want the same range A1:B8 for the whole area E1:G10, you can use absolute
addressing $A$1:$B$8.

You see the same behavior, if you use conditional formatting.
Comment 6 jbf.faure 2009-06-06 22:45:00 UTC
Perhaps this issue should be closed as invalid according to Regina's comment.

Regards