Problem with spreadsheet equations from eDoCreator

["G. Ken Holman" <gkholman@CraneSoftwrights.com>]

Hi, folks,

I've just found a problem with the spreadsheets and I would like to 
propose a fix.

A few of our spreadsheet columns are, in theory, formulae and not 
fixed values.  The eDoCreator export uses a formula in column A but 
not in any of the other columns ... all other columns are filled with 
string values.  Part of my massage of the eDoCreator spreadsheets is 
to put equations in all cells where equations belong.

I copied the formulae found in Column A into all rows of the massaged 
spreadsheet, only to discover at this 11th hour that eDoCreator 
*selectively* has a non-formula value in Column A.  So far, I have 
only found one such non-formula in rows named "UUID".  There may be 
others and I have to go looking for them somehow.  I managed only by 
chance to find the exception of UUID.

We need one formula to use in all rows, not a mixture of formulae in 
the column.

I'm using ApplicationResponse as an example spreadsheet.  Cell A8 is 
a fixed string, all other BBIE cells in column A are the same formula.

The formula used in column A in the other BBIE rows reads:

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(IF(E7="Universally 
Unique";"UU";E7);IF(G7<>I7;H7;F7);CONCATENATE(IF(I7="Identifier";"ID";IF(I7="Text";"";I7))));" 
";"");"'";"")

I propose we change the BBIE formula to be as follows:

=IF(G7="UUID";"UUID";SUBSTITUTE(SUBSTITUTE(CONCATENATE(IF(E7="Universally 
Unique";"UU";E7);IF(G7<>I7;H7;F7);CONCATENATE(IF(I7="Identifier";"ID";IF(I7="Text";"";I7))));" 
";"");"'";""))

My tests indicate the above works ... it is a pretty simple change 
... but we have to keep our eye out for any other exceptions.

BTW, the formula used in column A in ASBIE rows reads:

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(IF(E15="Universally 
Unique";"UU";E15);F15;IF(H15<>I15;H15;"");CONCATENATE(IF(I15="Identifier";"ID";IF(I15="Text";"";I15))));" 
";"");"'";"")

... and the formula used in cell A2 in the ABIE reads:

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(IF(C2="";"";CONCATENATE(C2;""));"";D2);" 
";"");"'";"")

Please let me know if you have any comments in this regard.

Thanks!

. . . . . . . . . . . Ken

--
Contact us for world-wide XML consulting and instructor-led training
Free 5-hour video lecture: XSLT/XPath 1.0 & 2.0 http://ude.my/uoui9h
Crane Softwrights Ltd.            http://www.CraneSoftwrights.com/o/
G. Ken Holman                   mailto:gkholman@CraneSoftwrights.com
Google+ profile: https://plus.google.com/116832879756988317389/about
Legal business disclaimers:    http://www.CraneSoftwrights.com/legal