<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
..MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:115174998;
mso-list-type:hybrid;
mso-list-template-ids:-1926702360 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal>DR-09-0014: SML: Incorrect restriction on the Name
Representation<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Hi everyone,<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Not everyone was able to attend Okinawa last month and so I
wanted to take some time today to write up a summary mail regarding this
particular defect for your review. I’ve incorporated some of the feedback
I received in Okinawa, so even for the folks who were able to attend that
meeting, there is some new information contained herein. <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>As background information for this defect, it is important
to remember that the intent of the IS 29500 standard was to fully encode
existing binary documents, binary spreadsheets and binary presentations using
XML. The latest document from Microsoft regarding these binary file
formats can be found here:<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal style='margin-left:.5in'><a
href="http://www.microsoft.com/interop/docs/officebinaryformats.mspx/">http://www.microsoft.com/interop/docs/officebinaryformats.mspx/</a><o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>The applicable documentation for this issue can be found in
the spreadsheet documentation. In particular, we’re addressing the
encoding of record 218h which is defined on page 144 in the PDF version.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><b>DR-09-0014<o:p></o:p></b></p>
<p class=MsoNormal>This issue deals with the scope of named cells within a
spreadsheet. <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Named cells are a common spreadsheet feature where one or
more cells in a spreadsheet can be given a “friendly” name which
makes it easier to reference those cells elsewhere in the spreadsheet. <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>For example, suppose you want to calculate the payment on a
loan and you want to evaluate the impact that various interest rates would have
on that payment. One way to do this would be to use a cell to represent
the interest rate, name that cell interestRate and then use that name in the formula
that calculates the payment. This way you could play with the value in
the cell and not have to modify the actual formula to see the impact it has on
the payment. <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>If we consider ThinkFree’s Office Calc 3
implementation, the customer experience is pretty simple: select the cell that
you will use for the interest rate and use the <b>Insert / Name / Define …</b>
menu. This will bring up a dialog that shows you any named cells found in
the spreadsheet. In the dialog you can provide the name for the selected
cell(s). At this point, any time you want to refer to the contents of
this cell, you can refer to is as “interestRate” as opposed to “A:B2”.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>An important observation here is that the scope of the named
cells is at the <i>workbook</i> level and not the <i>worksheet</i> level.
Given that worksheets can refer to cells in other worksheets, <u>the uniqueness
of named cells needs to be scoped at the workbook level</u>. Otherwise,
there could be two different cell ranges with the same name causing confusion
as to which named cell(s) value(s) should be used. Clearly this would be
a problem for data integrity and interoperability. <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Other vendors’ products such as OpenOffice.org Calc 3,
Corel Quatro Pro X4 and Microsoft’s Excel 2003 all have similar
implementations: they support the ability to name cells, they require workbook-level
uniqueness and they express named cells using the same manner described in the
binary file format document above. Because these four vendors have chosen
similar implementations, spreadsheets with named cells can be successfully passed
between these vendors’ products. <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>I’ve attached a sample file generated from Think Free’s
Office Calc 3 product in case folks would like to experiment with this
functionality and the associated interop scenarios themselves. Naturally
there are some bugs in the vendors’ implementations, but you can take the
attached Think Free file and open it up successfully in the other
implementations. The spreadsheets open, the named cells are persisted and
the function which consumes the named cells continues to work as
expected. <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Additionally, Apple’s iWorks ’09 Numbers can
consume files generated from such implementations and make use of those named
cells. While they have similar functionality to named cells, I would not
go so far as to say they have native support for authoring named cells ( I can
go into more detail if anyone cares ).<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>If we look at the IS 29500 standard, the prose states the
following ( italics mine ):<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal style='margin-left:.5in'>18.17.6.5 Name Representation<o:p></o:p></p>
<p class=MsoNormal style='margin-left:.5in'>A formula can contain one or more
names. These names shall be defined in the <i>Worksheet</i> part's XML with
each being the subject of a definedName element, inside a definedNames element.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Not only does this conflict with existing binary spreadsheets,
it also conflicts with the intent of the schema. The only valid parent
element for the definedNames element is the workbook element. I interpret
this such that the intent of the standard was to match the existing binary
spreadsheets’ design and scope the uniqueness of named cells to the <i>workbook</i>
and not the <i>worksheet</i>.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Given that the standard’s prose conflicts with the
standard’s schema, that the industry has a common implementation of this
that successfully enables interoperability through the exchange of binary spreadsheets
and that the vendors’ implementation is consistent with the binary
spreadsheet documentation, I would recommend we change the prose to scope the
uniqueness of named cells to the <i>workbook</i>. The updated text would look
as follows ( italics to emphasize changed text ):<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal style='margin-left:.5in'>18.17.6.5 Name Representation<o:p></o:p></p>
<p class=MsoNormal style='margin-left:.5in'>A formula can contain one or more
names. These names shall be defined in the <i>Workbook</i> part's XML with each
being the subject of a definedName element, inside a definedNames element.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Thanks for reading.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>shawn<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
</div>
</body>
</html>