Apache OpenOffice (AOO) Bugzilla – Issue 80778
data validity sheet references aren't by name
Last modified: 2013-08-07 15:12:27 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!
Hi jmberg! Place "$" in front of the sheet name. $Sheet2.$A$1:$A$5
Indeed; I wasn't aware that sheet references behaved as relative by default too. Thanks.
Please close this issue.
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]
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).
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.
I do not see any chance to change this behavior but as an enhancement requirements have to decide. Frank
Is it possible to add option like "resolve references to sheet by sheet name"?