Friday, April 1, 2011

Conditional formatting bug in Numbers

I love Numbers and use it daily.

However, there is one annoying bug that I've put up with for too long. I use conditional formatting in my accounting sheet to show alarting colors "if not equal to cell (reference)". What happens is that copy/pasting such cell does NOT correspondingly update the conditional format reference. All the pasted cells will compare to the original reference.

This is *absolute* behaviour usually marked as "$A$1" (the dollars) in spreadsheets. However, the conditional formatting UI does not show $'s and therefore indicates the reference is relative. Actually, it does not *allow* changing the type of reference (which imho it should).

Usage case for relative reference in conditional formatting (this is how I use it):

- You have sums from the spreadsheet and below them "known right" sums i.e. from bank slips. If the two sums are not the same, mark with red bold color. Condition: "if not equal to (cell above)".

Usage case for absolute reference in conditional formatting (I don't use this):

- You have a spreadsheet-wide setting for some threshold value, i.e. to mark expenses higher than it. Expense cells have "if higher than (absolute cell)" to highlight the ones you should focus on.

I do hope Apple fixes this. I've reported it to them today.

No comments: