Forum at OOoForum.orgThe Forum
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Conditional Formatting bug in Calc 2.4.1

Post new topic   Reply to topic Forum Index -> Calc
View previous topic :: View next topic  
Author Message

Joined: 09 Aug 2008
Posts: 1

PostPosted: Sat Aug 09, 2008 6:12 pm    Post subject: Conditional Formatting bug in Calc 2.4.1 Reply with quote

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"
Back to top
View user's profile Send private message
Warrick Nelson
OOo Advocate
OOo Advocate

Joined: 04 May 2004
Posts: 203
Location: Christchurch, New Zealand

PostPosted: Sat Aug 09, 2008 9:53 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website

Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Sun Aug 10, 2008 12:44 am    Post subject: Reply with quote

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.)
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic Forum Index -> Calc All times are GMT - 8 Hours
Page 1 of 1

Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group