Apache OpenOffice Community Forum

I have a spreadsheet where we insert sales totals for sales people. These numbers are automatically imported from another worksheet in the workbook. I have a formula at the bottom of each column that functions as an error checking mechanism. It compares the total from the column to the total on another sheet. If the result is anything other than zero (no difference) the cell will highlight in red. I've setup the same conditional formatting for the column header to make it easier to spot when scrolling through the spreadsheet. It works just fine but where it goes wrong is when I insert a new row. The formatting in the cell with the formula continues to work but the column header formatting does not. If the initial setup for conditional formatting references cell D100, after inserting a row, it will still reference that cell and not automatically follow the cell with the formula in it. I know M$ Excel does this very well. How do I force the conditional formatting to continue referencing the cell with the formula in it after a row is inserted?

Conditional Formatting Stops Working After Inserting Rows

Works for me with inserted new rows. It works perfectly with imported data from a registered data source, including conditional formatting chart ranges, adjacent formula ranges and everything without a single line of stupid Basic code.

These numbers are automatically imported from another worksheet in the workbook.

Automatically? A macro? Simply let your macro insert new rows and then dump the source range's DataArray over the target range.

Not a macro. It's a formula that scans the other worksheet by name and brings overs the associated sales figures.

=Numeric_Formula+STYLE(IF(condition1;"styleA";IF(condition2;"styleB";"styleC")))

This could work more elegantly with a database connection (which can point to another spreadsheet behind the database).

Click in D100 then click in the Name Box on the left side of the Formula Bar.
Type in a suitable name for D100 eg Total, then press Enter.
Use the defined name Total in the conditional formatting in place of D100.
The cells pushed down by the row insert take their name with them.

Ken Johnson

ken johnson wrote:Click in D100 then click in the Name Box on the left side of the Formula Bar.
Type in a suitable name for D100 eg Total, then press Enter.
Use the defined name Total in the conditional formatting in place of D100.
The cells pushed down by the row insert take their name with them.

Ken Johnson

I tried this and it seems to work just fine. Thanks!