Excel 2002 Error in Conditional Formatting with Relative Addresses

B

BJT

I recently switched from Excel 2000 to 2002 and have encountered a problem
with Conditional Formatting. I have a number of spreadsheets with tables
where cells have conditional formats based on a condition that contains a
relative row address such as the following in Cell B372

Formula Is =($B372<>$B371)

Now when I insert a row in the table (say at row 20), the old Cell B372 is
now B373, but the conditional format becomes

Formula Is =($B373<>$B371)

i.e. the second address in the formula is not updated - as though it was an
absolute cell reference. So now the conditional formats in all rows after row
20 are based on the cell two rows above instead of the preceding row - which
messes up all conditional formats in the table below the newly inserted row.
Anyone else encountered this problem ?
 
J

Jerry W. Lewis

=($B373<>OFFSET($B373,-1,0))
This is the way that all formulas have always worked. It is not unique
to either conditional formatting or Excel 2002.

Jerry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top