Excel 2007 Conditional formatting with number formats

  • Thread starter Thread starter Brian Charlton
  • Start date Start date
B

Brian Charlton

Simplified sample data below

A B
1
2
3 2 20
4 5 50
5 6 60
6 8 80


Formula in cell A3 =A3*10+IF($A$1=1,5,0)
and similarly copied to cells below
2 alternative conditional formats are applied to cells A3:A6
=$A$1=1 2.00 (2 decimal places)
=$A$1=0 2.0 (1 decimal places)
This resulted in the following alternative displays and values when either 1
or 0 was entered in cell A1

A B
1 1
2
3 2.00 25
4 5.00 55
5 6.00 65
6 8.00 85


A B
1 0
2
3 2.0 20
4 5.0 50
5 6.0 60
6 8.0 80


However if formula: =Sheet2!A1 - is entered in cell A1 and 1 or 0 then in
cell A1 on sheet2 the cell values change but the formats do not change as
required.

Results:

A B
1 1
2
3 2.0 25
4 5.0 55
5 6.0 65
6 8.0 85



A B
1 0
2
3 2.0 20
4 5.0 50
5 6.0 60
6 8.0 80


Fill works correctly. It is the new number formatting which does not. Any
thoughts?

I am also surprised that copy pasting special formats to cells in 2007 adds
to any conditional formats already in the cells and not replace it.
 
before you even get to conditional formatting - you have a circular
reference, do you really want that?
 
I note my circular reference. The formula was entered in B3 etc on the
worksheet and not A3.

If it is a known problem what is the solution. if any? Is there no hot fix
or update available? My Office Proffesional was only purchased in the last
6-8 weeks.
 
Back
Top