Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||data validity sheet references aren't by name|
|Component:||ui||Assignee:||AOO issues mailing list <issues>|
|Status:||CONFIRMED ---||QA Contact:|
|Priority:||P3||CC:||helenrussian, issues, kpalagin|
|Issue Type:||ENHANCEMENT||Latest Confirmation in:||---|
Description jmberg 2007-08-17 12:07:29 UTC
If you reorder sheets, data validity references change. Try this: * create a new spreadsheet document with three sheets * go to Sheet2 and enter 1, 2, 3, 4, 5 in to the range $A$1:$A$5 * go to Sheet3 and enter a, b, c, d, e in to the range $A$1:$A$5 * go to Sheet1 and in any cell enter a "Cell Range" data validity, using "Sheet2.$A$1:$A:$5" * verify that it shows you the values 1,2,3,4,5 in the drop down * reorder the sheets to the order Sheet1,Sheet3,Sheet2 * see that it shows the values a,b,c,d,e in the drop down!
Comment 1 helenrussian 2007-09-06 18:52:44 UTC
Hi jmberg! Place "$" in front of the sheet name. $Sheet2.$A$1:$A$5
Comment 2 jmberg 2007-09-07 15:23:36 UTC
Indeed; I wasn't aware that sheet references behaved as relative by default too. Thanks.
Comment 3 helenrussian 2007-09-07 17:46:49 UTC
Please close this issue.
Comment 4 jmberg 2007-09-07 20:22:50 UTC
I guess the behaviour can't be changed now, but I still think it's rather weird that relative references to cells are updated when you shift around cells e.g. by deleting columns, but relative sheet references aren't updated when you shift around sheets. That is, if you have four sheets (Sheet1,2,3,4) instead of the three in my example and make a Sheet3.$A$1:$A$5 reference, and then delete Sheet2, your reference will all the sudden point to Sheet4. If it had been a cell reference and you'd deleted a column, which I feel is somewhat equivalent, the cell reference will be updated. In fact, I guess that when you have a Sheet4.... reference and delete Sheet3 your reference all the sudden is totally invalid because the "current sheet + 3" doesn't exist any more. I think this behaviour is highly unintuitive, everywhere else are relative references updated when things change but with the sheets. However, I don't feel strongly about the issue either, I've only used the data validity feature once [and promptly ran into the issue, however]
Comment 5 kpalagin 2007-09-07 21:31:42 UTC
jmberg, I think you could change issue type to Enhancement (but do not hold you breath - requirements team is very slow to respond to RFEs).
Comment 6 jmberg 2007-09-07 21:41:56 UTC
Sure, doing that. It's not important to me, this is the only time I've used calc in a long time, don't do much with it anyway.
Comment 7 frank 2007-09-10 11:15:00 UTC
I do not see any chance to change this behavior but as an enhancement requirements have to decide. Frank
Comment 8 andrey_z 2010-03-12 11:24:26 UTC
Is it possible to add option like "resolve references to sheet by sheet name"?