Conditional Formatting bug in Calc 2.4.1

[waynemv]

In Open Office Calc 2.4.1 (tested under Windows Vista Pro), cell references within conditional formatting are not correctly updated when rows are inserted or deleted.

For example, let K5 have "$F5>=K$4" for conditional formatting then delete row 3 .
The resulting formatting in K4 (which used to be K5) changed to "$F4>=K$4" but it should have changed to "$F4>=K$3"

[Warrick Nelson]

K$4 will not change to K$3 as that is what the $ means (don't change the number dynamically). This has nothing to do with conditional formatting.

[keme]

To elaborate on W. Nelson's explanation:
In a spreadsheet, a reference to a cell is maintained if the cell moves (i.e. by dragging the cell, cut/paste, or by inserting a new cell range). Calc will always point to the new location as long as it's in the same file, regardless of where it is, and disregard the contents of the old location. If the cell is not moved to a new location, but rather removed, there is no cell to refer to, so any reference to it will be void. This is common behavior in spreadsheets.

Actually, when sorting a range in Calc, it seems that original addressing into the sorted range is maintained, rather than updated according to new positions of the elements. This may pertain to other range operations, too.

The functionality you request is that of a database. You really should use database methods for that. Things to have in mind when working with databases is:
Generally, do not refer to data records (rows) directly by position, only by content.
Specifically, look into the following if you still want to use a spreadsheet:

• Use the LOOKUP(), VLOOKUP() and HLOOKUP() functions or data filtering to extract data.
  
• For more advanced/flexible data search/extraction, you can use the MATCH() function to determine current position of the element to look up, and then the INDEX() or OFFSET() function to extract data.
  
• Use the CELL(), ROW() and COLUMN() to determine current position. Then you can INDEX() to the cell's immediate surroundings if you really need that.
  
• Use data validation to preserve data integrity.

You should also avoid complex formulas in the conditional formatting. Establish an "indicator" field where you do the calculations needed to decide the format (so you keep the "markup property" within the single row), then use that field for your conditional format. That will be much easier to work with. (Remember, the indicator column can be hidden if you want.)