Excel 2010 Data Validation List on separate worksheet without named range

Topics: Developer Forum
Mar 19, 2012 at 4:42 AM
Edited Mar 19, 2012 at 4:44 AM

So I noticed that, in Excel 2010, users can create Data Validation List against a range in another worksheet without relying on a named range.  I also noticed that, when users inadvertently rely on this new functionality for their cell validation, the Excel2007 Reader doesn't pick up the validation.  I looked at the worksheet xml to see what was going on and found this (I have named range list validation on B4 and new transparent direct-reference validation on B7):

	<dataValidations count="1">
		<dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B4">
			<formula1>lookup</formula1>
		</dataValidation>
	</dataValidations>
	<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
	<pageSetup orientation="portrait" horizontalDpi="0" verticalDpi="0" r:id="rId1"/>
	<extLst>
		<ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
			<x14:dataValidations count="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
				<x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
					<x14:formula1>
						<xm:f>lookup!$A$2:$A$4</xm:f>
					</x14:formula1>
					<xm:sqref>B7</xm:sqref>
				</x14:dataValidation>
			</x14:dataValidations>
		</ext>
	</extLst>

I could probably write up the piece of simpleXML object parsing to jam these new Microsoft-hacked data validations into the existing dataValidation object.  It is sad because it seems like Microsoft made progress with the whole openXML approach for 2007 and then started jamming stuff in with this new extLst tag and the x14: namespaced children - I'm sure they had their reasons.  I don't know if there are a bunch more properties/features that are 2010 specific that live under this extLst node - for my current project I really just need the data validation.  Is a 2010 reader already in the works?  If I do work something up should I share it and if so where?

Thanks for taking a look at this!

Alex

Mar 19, 2012 at 4:54 AM

Quick bit of Googling revealed that all of this is in the new Office2010.excel namespace.  Cute.  Look forward to reply on questions above.