Data Validation on existing Excel documents being ignored by Excel 2007

Can you post a dumbed down version that still exhibits the behaviour?

The issue is with historical documents that used to work and now dont. If i edit them it wont help with the issue. I cannot post an existing document as they have confidential information in them, and new documents created in exactly the same way dont have this issue. I could post screenshots with the settings of the same document viewed in both 2007 and 2010 with the information removed if that helps?

I am not sure then...


Can you check the data validation rule?  Is it referencing a named range (e.g. =MyRange) or an actual range (e.g =Sheet2!A1:A100).

They referenced actual ranges. The weird thing is that when i view the data validation setting for any of the cells in the document, they are not there in Excel 2007. The same document in Excel 2010 has the Data Validation's showing correctly, so i know the document hasnt been changed.

Try instead naming the range you are referencing and then use that name in the data validation: e.g. =MyList

Does that work?

Yes it does, which is a solution moving forward. It also works the actual range method on new documents, but this issue is occuring in documents that were created weeks/months ago, and were working and viewing fine up until late last week. The customer doesnt want to recreate all their previous spreadsheets (as they have hundreds of them), and in their eyes is a software issue.

Hi, pyranetuk.

A long shot, but please try the following...
(1) Take your "8 week" file (assuming you still have an unchanged version). Use your favourite zip program to extract from its xl\worksheets folder the xml file for one of the sheets with Data Validation. (Careful, the file name will be the sheet's Code Name which may be different from that displayed in the tab).
(2) Using you favourite text editor, find and extract everything between "<dataValidations" and "</dataValidations>". (Please check that there is only one of each.)
(3) After you've checked that there's nothing confidential in the extract, please post it here.
(4) Now repeat steps (1) to (3) for a file that is working in 2007.

If we're lucky, there'll be an obvious and significant difference between the two extracts.

Thanks,
Brian.

File thats been unchanges with the issue....

-<dataValidations count="1"><dataValidation sqref="M49 F144 F141 C134:F134 F130 D130 L130:M130 M129 F304 F301 C294:F294 F290 D290 L290:M290 M289 F264 F261 C254:F254 F250 D250 L250:M250 M249 F224 F221 C214:F214 F210 D210 L210:M210 M209 F184 F181 C174:F174 F170 D170 L170:M170 M169 F104 F101 C94:F94 F90 D90 L90:M90 M89 F64 F61 C54:F54 F50 D50 L50:M50" showErrorMessage="1" showInputMessage="1" allowBlank="1"/></dataValidations><printOptions horizontalCentered="1"/><pageMargins footer="0.39370078740157483" header="0.23622047244094491" bottom="0.59055118110236227" top="0.35433070866141736" right="3.937007874015748E-2" left="3.937007874015748E-2"/><pageSetup r:id="rId1" orientation="landscape" fitToHeight="100" scale="82" paperSize="9"/>-<headerFooter alignWithMargins="0"><oddFooter>&L&"-,Regular"Project No: BGL/0080MO/SA&C&"-,Regular"IM2/SRS/October/2010&R&"-,Regular"Appendix (i) - Page &P of &N</oddFooter></headerFooter>-<rowBreaks count="7" manualBreakCount="7"><brk max="16383" id="36" man="1"/><brk max="12" id="76" man="1"/><brk max="12" id="116" man="1"/><brk max="12" id="156" man="1"/><brk max="12" id="196" man="1"/><brk max="12" id="236" man="1"/><brk max="12" id="276" man="1"/></rowBreaks><drawing r:id="rId2"/>-<extLst>-<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}">-<x14:dataValidations count="9" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main" disablePrompts="1">-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$Q$1:$Q$12</xm:f></x14:formula1><xm:sqref>C48:C49 C88:C89 C168:C169 C208:C209 C248:C249 C288:C289 C128:C129</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$A$2:$A$18</xm:f></x14:formula1><xm:sqref>E48 E88 E168 E208 E248 E288 E128</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$B$23:$B$37</xm:f></x14:formula1><xm:sqref>D43:D45 D83:D85 D163:D165 D203:D205 D243:D245 D283:D285 D123:D125</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$D$2:$D$6</xm:f></x14:formula1><xm:sqref>D48:D49 D88:D89 D168:D169 D208:D209 D248:D249 D288:D289 D128:D129</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$G$2:$G$6</xm:f></x14:formula1><xm:sqref>F48 F88 F168 F208 F248 F288 F128</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$D$25:$D$29</xm:f></x14:formula1><xm:sqref>C53 C93 C173 C213 C253 C293 C133</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$J$25:$J$29</xm:f></x14:formula1><xm:sqref>E53 E93 E173 E213 E253 E293 E133</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$G$25:$G$29</xm:f></x14:formula1><xm:sqref>D53 D93 D173 D213 D253 D293 D133</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$M$25:$M$29</xm:f></x14:formula1><xm:sqref>F53 F93 F173 F213 F253 F293 F133</xm:sqref></x14:dataValidation></x14:dataValidations></ext></extLst>

Select allOpen in new window

New document created in Excel 2007 that works fine...

-<dataValidations count="1">-<dataValidation sqref="A2" showErrorMessage="1" showInputMessage="1" allowBlank="1" type="list"><formula1>Sheet2!A2:A10</formula1></dataValidation></dataValidations>

Select allOpen in new window

Thanks, pyranetuk.

Please check the first entry - it may be truncated.

Edit:FWIW, the length of that funny first entry is 255 - probably significant. It doesn't appear to be an Experts Exchange line length limit...

-<dataValidations count="1"><dataValidation sqref="M49 F144 F141 C134:F134 F130 D130 L130:M130 M129 F304 F301 C294:F294 F290 D290 L290:M290 M289 F264 F261 C254:F254 F250 D250 L250:M250 M249 F224 F221 C214:F214 F210 D210 L210:M210 M209 F184 F181 C174:F174 255 plus a few more characters.

Select allOpen in new window

Edit2: And do the XML files have the first entry's leading hyphen and the second entry's hyphens between the ">" and "<"?

Regards,
Brian.

I've reattached the xml code, just incase it got cropped...

<dataValidations count="1"><dataValidation sqref="M49 F144 F141 C134:F134 F130 D130 L130:M130 M129 F304 F301 C294:F294 F290 D290 L290:M290 M289 F264 F261 C254:F254 F250 D250 L250:M250 M249 F224 F221 C214:F214 F210 D210 L210:M210 M209 F184 F181 C174:F174 F170 D170 L170:M170 M169 F104 F101 C94:F94 F90 D90 L90:M90 M89 F64 F61 C54:F54 F50 D50 L50:M50" showErrorMessage="1" showInputMessage="1" allowBlank="1"/></dataValidations><printOptions horizontalCentered="1"/><pageMargins footer="0.39370078740157483" header="0.23622047244094491" bottom="0.59055118110236227" top="0.35433070866141736" right="3.937007874015748E-2" left="3.937007874015748E-2"/><pageSetup r:id="rId1" orientation="landscape" fitToHeight="100" scale="82" paperSize="9"/>-<headerFooter alignWithMargins="0"><oddFooter>&L&"-,Regular"Project No: BGL/0080MO/SA&C&"-,Regular"IM2/SRS/October/2010&R&"-,Regular"Appendix (i) - Page &P of &N</oddFooter></headerFooter>-<rowBreaks count="7" manualBreakCount="7"><brk max="16383" id="36" man="1"/><brk max="12" id="76" man="1"/><brk max="12" id="116" man="1"/><brk max="12" id="156" man="1"/><brk max="12" id="196" man="1"/><brk max="12" id="236" man="1"/><brk max="12" id="276" man="1"/></rowBreaks><drawing r:id="rId2"/>-<extLst>-<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}">-<x14:dataValidations count="9" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main" disablePrompts="1">-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$Q$1:$Q$12</xm:f></x14:formula1><xm:sqref>C48:C49 C88:C89 C168:C169 C208:C209 C248:C249 C288:C289 C128:C129</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$A$2:$A$18</xm:f></x14:formula1><xm:sqref>E48 E88 E168 E208 E248 E288 E128</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$B$23:$B$37</xm:f></x14:formula1><xm:sqref>D43:D45 D83:D85 D163:D165 D203:D205 D243:D245 D283:D285 D123:D125</xm:sqref></x14:dataValidation>+<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">--<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$G$2:$G$6</xm:f></x14:formula1><xm:sqref>F48 F88 F168 F208 F248 F288 F128</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$D$25:$D$29</xm:f></x14:formula1><xm:sqref>C53 C93 C173 C213 C253 C293 C133</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$J$25:$J$29</xm:f></x14:formula1><xm:sqref>E53 E93 E173 E213 E253 E293 E133</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$G$25:$G$29</xm:f></x14:formula1><xm:sqref>D53 D93 D173 D213 D253 D293 D133</xm:sqref></x14:dataValidation>-<x14:dataValidation type="list" showErrorMessage="1" showInputMessage="1" allowBlank="1">-<x14:formula1><xm:f>'Reference Sheet'!$M$25:$M$29</xm:f></x14:formula1><xm:sqref>F53 F93 F173 F213 F253 F293 F133</xm:sqref></x14:dataValidation></x14:dataValidations></ext></extLst>

Select allOpen in new window

pyranetuk,

Thanks, that's been really useful. Besides the ordinary "<DataValidations>", the entry also shows "<x14:dataValidations>". I googled this and got a small no. of hits, one of which is this MS page where it says of these entries...
Defines the DataValidations Class.This class is available in Office2010 or above.

This page says that these entries can be created if you "create a Data Validation List against a range in another worksheet without relying on a named range". That may well be the case here.

(1) Please check if this file has Data Validation referencing another sheet without using Names.
(2) In case that isn't the situation, is it possible that these files have been generated by something other than Excel?
(3) Using 2010, please check the compatibility of one of these files. (On the Ribbon's Menu Bar select "File", then "Info", then "Check for Issues" then "Check Compatibility".)

Thanks,
Brian.

Hi Brian,

Sorry for the delay in getting back to you on this - been trying to get hold of our client to talk to them about it.

To answer your questions...

1) This is the case, they reference cells in another worksheet (for example, sheet2) within the same document.

2) The client insists the files were created by Excel 2007, and has been untouched by Excel 2010.

3) I used Excel 2013 to check for compatibility, and it does flag the cells for datavalidation as being incompatible with Excel 2007, as well as 97-2003.

I asked the client to send me another document, and this too had the </x14:dataValidation> tags within the XML. The client insists its impossible that these files have been opened by Excel 2010 at any point, so I'm in a difficult position as I believe they have been.


Bottom Line I guess is that I need to know if its at all possible for them tags to be present without ever being opened in Excel 2010/2013?

Many Thanks.
Simon

Simon,

Thanks for all that, the Compatibility check pretty much confirms the situation.

(1) I asked you to check whether another program was used to create the files, I should also have asked about "update" - can you confirm that no non-Excel application updated the files? (No chance that someone else in the department was industriously updating a logo, company name, confidentiality notices, etc.?)

(2) Let's assume that the client is correct about 2010 never having touched the files. Then, some bug in Excel 2007 (maybe Compatibility Pack related?) has been quietly adding these attributes to the files and using them for many months.  Overnight (?), 2007 suddenly went back to normal and stopped recognising them. Can you get a list of Office Updates done in, say, the week before the problem surfaced?
Don't lose these files as we'll probably want to do other things with them!

(3) As I understand it, the affected files have last-modified dates going back over a long period (several months at least) - well before this problem started happening. For one of the affected files, could you get its current version and a backup copy from, say, six months ago, and check whether they both have the compatibility problem? (Please make sure that the client doesn't open the backup file or re-save the current version before sending them and that you make both read-only before opening them.)

(4) Any chance that the client has an "empty" file with the problem that you could post here?

Edit: Just noticed your mention of 2013 - good call! So, please read 2010 as "2010/2013". (Is your client planning a move to 2013?)

Thanks,
Brian.

Hi Brian,

Yes, I have 2013 on my own laptop, getting used to it slowly, as well as windows 8! :)

I've been speaking to the client again, trying to force the issue and get the correct information out of them. It turns out, that the documents were in fact edited early this year by a user that has Excel 2010.

The client is insisting though that until recently, they could see the data validation dropdown menus in Excel 2007. I don't think that was possible, so I've setup a vanilla Windows XP SP2 with Office 2007 SP0, no updates installed, in a new virtual machine. I opened the documents and they don't work (as I expected to be the case).

so I think the final question is, is it possible at all for 2007 to see those data validations made by 2010? SP0 and SP3 cannot see them, but maybe somewhere in between could see them?

I feel I'm clutching at straws here, but I need something more palatable than "it is impossible that you were using 2007 for nearly a year in that way", as it sounds like I'm accusing the customer of pulling a fast one :)

Regards,
Simon

Thanks, Simon. What was the resolution of the issue?