I am creating Excel sheets in the somewhat ancient 2003 SpreadsheetML flavour (mainly because everything is contained in one single file). Trying to implement a selection list drives my crazy - either due to my stupidity or to a lack of documentation.
Have a look at this (works nicely with recent versions of Excel):
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<Worksheet ss:Name="input">
<ss:Table>
<ss:Row>
<ss:Cell><ss:Data ss:Type="String"/></ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell><ss:Data ss:Type="String">Anna</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type="String">Berta</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type="String">Claudia</ss:Data></ss:Cell>
</ss:Row>
</ss:Table>
<DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
<Range>R1C1</Range>
<Type>List</Type>
<Value>input!R2</Value>
</DataValidation>
</Worksheet>
<Worksheet ss:Name="check">
<ss:Table>
<ss:Row>
<ss:Cell><ss:Data ss:Type="String"/></ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell><ss:Data ss:Type="String">Alfred</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type="String">Bruno</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type="String">Clemens</ss:Data></ss:Cell>
</ss:Row>
</ss:Table>
</Worksheet>
</Workbook>
Two worksheets containing a list of names in Row 2, plus one single cell with a simple validation rule: input!A1 may only contain one of the names "Anna", "Berta" or "Claudia". Works as expected.
But then I change the validation to what I actually want to achieve (i.e. use the list from the other worksheet):
<Value>check!R2</Value>
This still loads into Excel, but there is no dropdown. If I look at the validation rules, Excel claims a "reference error" (or similar - I am using German locales), even though I cannot find any error in "check!R2". I can correct this manually to check!$2:$2 (the UI way of cell referencing), I can save the result into an XML file, and the result is exactly the "check!R2" which I tried without success...
But as soon as I open the (saved) file, the reference is invalid again.
This drives me nuts - there must be some way to validate against values from another worksheet. Unfortunately there is not too much documentation available (and actually never has been); one of my sources is https://schemas.liquid-technologies.com/Office/2003/?page=excelss_xsd.html which lists all the available tags but not the details of their semantics.
Any ideas anyone?
That old 2003 XML schema should be avoided. But if used, then one can only use features which were available in year 2003 too. So @Rory is correct in his comments. In 2003 Excel was not able getting data validation lists from other sheets directly. Named ranges had must be created which refers to the other sheet. And those Names then had been referenced by the datavalidation list.
So following will work:
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<Names>
<NamedRange ss:Name="ceckRow2" ss:RefersTo="=check!R2"/>
</Names>
<Worksheet ss:Name="input">
<ss:Table>
<ss:Row>
<ss:Cell><ss:Data ss:Type="String"/></ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell><ss:Data ss:Type="String">Anna</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type="String">Berta</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type="String">Claudia</ss:Data></ss:Cell>
</ss:Row>
</ss:Table>
<DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
<Range>R1C1</Range>
<Type>List</Type>
<Value>ceckRow2</Value>
</DataValidation>
</Worksheet>
<Worksheet ss:Name="check">
<ss:Table>
<ss:Row>
<ss:Cell><ss:Data ss:Type="String"/></ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell><ss:Data ss:Type="String">Alfred</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type="String">Bruno</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type="String">Clemens</ss:Data></ss:Cell>
</ss:Row>
</ss:Table>
</Worksheet>
</Workbook>