[Solved] Problem Sorting Data with Conditional Formatting

[Cartaphilus]

I am new to Open Office and it's very possible I'm doing something wrong or attempting to do something that can't be done. So before I go screaming "Bug! Bug!", I'd like to check with the forum.

I have a fairly simple spreadsheet with a column of names and a column of dates. I have a cell in another column outside of these two columns which is equal to today() [for this example, assume it is cell D1]. The cells in the date column are conditionally formatted to alert me to dates that have expired. That is, each cell in the date column has a conditional format of "If cell value is less than or equal to D1 then result".

If I sort the names and dates columns by dates ascending, the names and dates are put in the proper order, but the comparison value in the conditional formatting statement changes to what seems to be a random value. That is, if the conditional formatting statement is as described above before the sort, after the sort it might be "If cell value is less than or equal to D8 then result". There isn't any logic that I can see as to what the cell gets changed to.

Am I do something wrong? Is there a logical reason why this is happening? Or is there a bug in the sort function WRT conditional formatting?

TIA...

[jrkrideau]

I am new to Open Office and it's very possible I'm doing something wrong or attempting to do something that can't be done. So before I go screaming "Bug! Bug!", I'd like to check with the forum.

I have a fairly simple spreadsheet with a column of names and a column of dates. I have a cell in another column outside of these two columns which is equal to today() [for this example, assume it is cell D1]. The cells in the date column are conditionally formatted to alert me to dates that have expired. That is, each cell in the date column has a conditional format of "If cell value is less than or equal to D1 then result".

If I sort the names and dates columns by dates ascending, the names and dates are put in the proper order, but the comparison value in the conditional formatting statement changes to what seems to be a random value. That is, if the conditional formatting statement is as described above before the sort, after the sort it might be "If cell value is less than or equal to D8 then result". There isn't any logic that I can see as to what the cell gets changed to.

Am I do something wrong? Is there a logical reason why this is happening? Or is there a bug in the sort function WRT conditional formatting?

TIA...

I'm not sure I get the total layout here but how are you sorting the data? There have been problems using the sort icon on the tool bar for some sorting issues. Try Data > Sort and see if that helps.

[acknak]

It seems to work ok for me--here's a working sample.

Can you provide a sample that fails for you?

It is very easy in Calc for sorting to "go bad" -- e.g., to automatically pick the wrong cells to sort, or to sort on the wrong column.

It can also be tough to get the conditional formatting formula correct so that it refers to the right cells even if the formula is moved around by the sort.

[Cartaphilus]

Sorry, should have given a better description.

OK, look at Part 1, attached. Cell A9 is set to the formula '=today()'. Each of the dates in column B has conditional formatting set to reference cell A9 (as documented in column D). I manually drag and select columns A and B, rows 1 to 5. Then I click on Data->Sort, select column B ascending, and Part 2 shows the results. Two of the cells have their conditional formatting reference changed to cell A13...?

[FJCC]

The important difference between your example and acknak's is that acknak uses an absolute reference to the cell that contains the the Today() value. If you change your conditional format to Less Than or Equal To $A$9, the sorting will preserve the reference.

[Cartaphilus]

Bingo! It was the relative references that were causing the problem. When I reformatted to absolute references, the sort worked as expected.

Thanks to everyone who replied.