Excel 2007 Conditional formatting with number formats
 

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.

--
Brian Charlton

Excel 2007 Conditional formatting with number formats
 

before you even get to conditional formatting - you have a circular
reference, do you really want that?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Brian Charlton" wrote:

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.

--
Brian Charlton

Excel 2007 Conditional formatting with number formats
 

hi,

this is a known bug.


It works for me.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Brian Charlton" wrote:

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.

--
Brian Charlton

Excel 2007 Conditional formatting with number formats
 

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.
--
Brian Charlton


"Shane Devenshire" wrote:

before you even get to conditional formatting - you have a circular
reference, do you really want that?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Brian Charlton" wrote:

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.

--
Brian Charlton