Inconsistent conditional formatting

[Jack Schitt]

Hi all
Office XP service pack 2 (pack 3 not yet installed)

I have a workbook that contains cells that are subject to conditional
formatting.

By way of example, Condition 1 of cell Q9 is set to
=AND($D9>0,ISNA(MATCH($Q9,L_Charity,0)))

If I select cell Q9 and then from the menu bar select
Format/Conditional Formatting, then Condition 1 changes to:
=AND(#REF!>0,ISNA(MATCH(#REF!,LCharity,0)))

If I click on "OK", and then repeat the process, then it reverts back to
=AND($D9>0,ISNA(MATCH($Q9,L_Charity,0)))

It seems to alternate.

When this sort of thing happens generally, my first thoughts are
1) Circular referencing?
2) Manual recalculation?
3) Iteration switched on?

I have checked these and note that :
Iteration is not switched on
Calculation is set to fully automatic
No "Circ" warning message is displayed.

Is this a bug in Excel that might be solved by Service Pack 3?
Is it likely that the workbook is in some way corrupt?
Or finally
Is there something in the formulae that could be creating this effect?
If the last, any ideas about how to go looking for it?

Thanks

 

[BrianB]

I would guess that at some stage you changed the name of range LCharit
(which produces the #REF) to L_Charity

 

[Jack Schitt]

Thanks for that. My post contained a misprint, sorry. L_Charity never got
changed. Only references to specific cells got alternated between the cell
ref and #REF!