Issue 80778

Summary: data validity sheet references aren't by name
Product: Calc Reporter: jmberg <johannes>
Component: uiAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: helenrussian, issues, kpalagin
Version: OOo 2.2.1   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---

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"?