Bug: Array formulas in conditional formatting ignored on XMLSpreadsheet load

[richardtallent]

My Google search came up dry...

Conditional Formatting rules are *supposed* to treat formulas as array
formulas.

But when loading an XML Spreadsheet file, conditional formatting rules
are *not* treated as array formulas, thus breaking the formatting. At
least in Excel 2003, haven't tested others.

Workarounds I've considered:

- After loading, select the the formatted range, go to Format :
Conditional Formatting, and hit OK. Then it works fine, no
modification to formulas needed. (not an option here, unsophisticated
users)

- Use the native Excel format (not an option here, server-generated
content)

- Upgrade to Office 2007 and the newer XML format (not an option here,
corporate thing)

- Avoid array formulas in conditional formatting rules (not an option
here, need multiple conditions and too many cells to use )

- Use a UDF in the conditional formatting rules (doesn't work--XML
Spreadsheets can't have UDFs, and conditional formatting rules can't
refer to add-in UDFs).

The only other thing I can think of would be a macro in a separate add-
in file that would select each range with conditional formatting and
re-apply it somehow.

Any ideas for other workarounds, or a hint on the code to try to re-
apply the conditional formatting ranges?

 

[Bob Phillips]

I have XL2003, and array formulae work fin in CF for me. You cannot have
array constants, but other arrays work fine.

Give us an example that doesn't work.

 

[richardtallent]

I have XL2003, and array formulae work fin in CF for me. You cannot have
array constants, but other arrays work fine.

Give us an example that doesn't work.

Ok, I've narrowed down the test case.

Create a spreadsheet with the following grid:

A B C
1 N N N
2 x x x
3 x x x

Create the following conditional format formula on cells A1:C1 (your
choice of pretty colors for the format):

=(SUM(($A1:$C1="N")*($A$2:$C$2=A$3))>0)

This is an array formula that activates cells in A1, B1, or C1
whenever (1) any of those three cells has an
"N", and (2) row 2 of the column with the "N" is equal to row three of
the current column.

(Read that a few times...)

And it works... you can alter the N's or the x's in any way you want
and the formatting works just fine.

Ok, now save as an XML Spreadsheet. Close Excel, and re-open the file.

Uh-oh, conditional formatting is broken! Now, you can only activate
conditional formatting if A1 is an
"N" and A2 is the same as A3, B3, or C3. The values of B1, B2, C1, and
C2 have no effect on the
conditional formatting.

Now, select A1:C1 and look at the conditional formatting formula.
Exactly the same as before, right?

Hit OK.

Conditional formatting starts working again, and will work during the
entire session the file is open.

So, my belief is that, on loading an XML spreadsheet, conditional
formats are NOT assumed to be array formulas, but when they are
entered via the Conditional Formatting dialog, they are.

And, unfortunately, the XML Spreadsheet schema has no facility for
explicitly stating a conditional format formula is an array formula.
(I tried hard-coding braces around the formula in the XML, just in
case, and that didn't work either.)

--Richard