Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting - mutlple formats | Excel Worksheet Functions | |||
copying conditional formats (2007 Beta) | Excel Worksheet Functions | |||
Number of Conditional Formats | Excel Worksheet Functions | |||
How do I do conditional formatting on number formats not patterns. | Excel Discussion (Misc queries) | |||
Conditional Number Formats | Excel Discussion (Misc queries) |