Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting using part of the cells contents
Hi,
i'm doing some MPG calculations at the moment and using a formula from a seperate worksheet which then displays on the first worksheet (="You average "&Round(&Sheet2!A4,2)&" MPG") and i want to format that cell (that displays the "Your Average is xx.xx MPG) so that if, for example, my average MPG is <=10 then the whole cell is red, 10 <30 Yellow and 30 Green i hope this makes sense... Cheers for your help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting using part of the cells contents
Hi,
use conditional formating, where the formula is, in the first option enter =A2<=10 choose green 2nd option =And(A210,A2<30) choose yellow third option enter =A230 choose red "Duey" wrote: Hi, i'm doing some MPG calculations at the moment and using a formula from a seperate worksheet which then displays on the first worksheet (="You average "&Round(&Sheet2!A4,2)&" MPG") and i want to format that cell (that displays the "Your Average is xx.xx MPG) so that if, for example, my average MPG is <=10 then the whole cell is red, 10 <30 Yellow and 30 Green i hope this makes sense... Cheers for your help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting using part of the cells contents
Try using "cell Value is" between 0 and 10
" " 11 and 19 etc. Hope this helps "Duey" wrote: Hi, i'm doing some MPG calculations at the moment and using a formula from a seperate worksheet which then displays on the first worksheet (="You average "&Round(&Sheet2!A4,2)&" MPG") and i want to format that cell (that displays the "Your Average is xx.xx MPG) so that if, for example, my average MPG is <=10 then the whole cell is red, 10 <30 Yellow and 30 Green i hope this makes sense... Cheers for your help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting using part of the cells contents
you do need conditional formatting but you want to use 'formula is' and base
the formula on sheet2!a4 (or wherever you mpg figure is calculated) but conditional formatting wont let you refer directly to a cell on another sheet so the easiest way is to name that cell (in your example: select Sheet2!a4 and click insert, name, define, "MPG", ok) once this cell is named the following will work:- if your result is in sheet1!a1 select it, click format, conditional formatting and change from 'cell value is' to 'formula is' then put =MPG<10, set format to red add another option and do =MPG30, set format to red, add another option and put =MPG<0 and set format to yellow (this is the default so as long as the other two arent true this will apply) Hth RegMigrant "Duey" wrote: Hi, i'm doing some MPG calculations at the moment and using a formula from a seperate worksheet which then displays on the first worksheet (="You average "&Round(&Sheet2!A4,2)&" MPG") and i want to format that cell (that displays the "Your Average is xx.xx MPG) so that if, for example, my average MPG is <=10 then the whole cell is red, 10 <30 Yellow and 30 Green i hope this makes sense... Cheers for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting using part of the cells contents | Excel Discussion (Misc queries) | |||
Conditionally formatting just part of a cell contents - how? | Excel Worksheet Functions | |||
Extract Part of a Cells Contents Q | Excel Worksheet Functions | |||
Conditional Formatting part 2 | Excel Worksheet Functions | |||
conditional formatting "if part of cell contents contains string" | Excel Worksheet Functions |