conditional formatting for ranges

Not sure if this is a flexcel question. We have a conditional formatting for a range of cells based on a formula. Eg. E12 in this attached output. It is supposed to display ?-? if the sum of the specified range is > 0. Otherwise display nothing. In the output, if you enter, say 10, E10:G18 should display ?-?. But it is not happening even when we do it manually in the resulting output. It only happens in one row not the whole range. Is this a known issue? Is this an excel issue?

Details of the conditional formatting.

Applies to,

Is there anything we are doing wrong?

It is hard to tell with just this information, but my guess is that you are misplacing some $ in the formula:


Should it be $E$5:$E$6, etc? Or maybe E5:E6 instead?  Or do you really want the column absolute and the row relative?

To go more in depth, when you have a $ before a column or a row, then it is that column/row for all formulas in the range.
If in cell B2 you have 
$A$1: This is cell A1
A1: This is cell B2
$A1 (what you have): this is cell A2.

But it is different when looking at the formula in Excel or FlexCel. In FlexCel, if the formula is A1, that means The cell where the formula is in. If the formula is in E4, then A1 means E4. A2 means E5 and so on. But if the formula is in B3, then A1 means B3, A2, B4 and so.

In Excel, while internally it is stored the same way (because it is the only way to do it), tries to "help" when you look at the cell. If you put the cursor at E4 and look at the formula, it might say "E4", not A1 (which is what is internally stored). It does that conversion so it seems more intuitive to you (After all, you want to refer to E4, not A1), but internally this is stored as A1. This is because the formulas in conditional formats are all relative, so A1 means an offset of (0,0). The real formula will change depending on what cell you are in.

FlexCel doesn't have an "active cell" where you press F2, so it will always use A1 for offset(0,0). Excel will also use it in the file, but it will use "top-left cell in the CF range for offset(0,0)when looking at a formula in a cell.

Maybe this can make it a little more clear. In Excel, select B2 and B3. Go to conditional format, add a new rule, use a formula and write "=B2=3"

Now move to B3, and edit the formula: You'll see it still says =B2=3, even when for cell B3, the actual formula being used is "=B3=3".

So to recap: Formulas in conditional formats are always relative. In FlexCel, A1 means no offset. In Excel "First cell in the range"  (B2 in the screenshot above) means no offset. At least when Excel displays the formula. Internally, it is saved as =A1=3.

Well, I don't know if this cleared up things or confused them more :)  If you are still having trouble, please send me a file showing what you see to and I'll take a look.

I didn't  mentioned negative offsets, which wrap the range all around... (so an offset of -1, -1 is cell XFD1048576) This is actually problematic when working with xls files (which have 256 columns and 65536 rows) and xlsx files (which have 16000 columns and 1048576 rows) 

in the "Conditional Formats might become invalid" part.