Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - 3 Rules - Excel 2000
Hi, I am using Excel 2000 and need to use Conditional Formatting to visually
represent areas of concern. I have one worksheet holding the core data, linked to a 2nd sheet which provides a summary where the formatting is used. I am using 3 colours to highlight data according to their value, Green, Yellow, Red, with the following settings: Green: 90 or above Yellow: Between 60 and 90 Red: Between 1 and 60 So if 91 is in cell a1, the cell automatically changes to Green and so forth. The conditional formatting works fine when data is entered directly into the cell. However, when it is linked and the linked cell has no data in it, it automatically turns Green when the formatting is applied. When i put zero in on the data sheet, it comes up with a #DIV/0! error msg. I have used the following to suppress the error msg so nothing comes up, but it fails to correct it. =IF('sheet2!F4="","",'sheet 2'!F3/'sheet2'!F4) So if cell 'sheet2!F4 is blank, then nothing is input, otherwise process F3/F4. To me, it looks like a simple process, but Excel doesnt like it. If someone could shine some light on this, that would be much appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - 3 Rules - Excel 2000
Include a concurrent test with ISNUMBER
(to avoid spurious triggers where the source data may contain text) Eg, for the green trigger, use Formula is: =AND(ISNUMBER(A1),A1=90) -- Max Singapore http://savefile.com/projects/236895 Downloads: 15,500, Files: 352, Subscribers: 53 xdemechanik --- "AT" wrote: Hi, I am using Excel 2000 and need to use Conditional Formatting to visually represent areas of concern. I have one worksheet holding the core data, linked to a 2nd sheet which provides a summary where the formatting is used. I am using 3 colours to highlight data according to their value, Green, Yellow, Red, with the following settings: Green: 90 or above Yellow: Between 60 and 90 Red: Between 1 and 60 So if 91 is in cell a1, the cell automatically changes to Green and so forth. The conditional formatting works fine when data is entered directly into the cell. However, when it is linked and the linked cell has no data in it, it automatically turns Green when the formatting is applied. When i put zero in on the data sheet, it comes up with a #DIV/0! error msg. I have used the following to suppress the error msg so nothing comes up, but it fails to correct it. =IF('sheet2!F4="","",'sheet 2'!F3/'sheet2'!F4) So if cell 'sheet2!F4 is blank, then nothing is input, otherwise process F3/F4. To me, it looks like a simple process, but Excel doesnt like it. If someone could shine some light on this, that would be much appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - 3 Rules - Excel 2000
You're missing a quote mark (or you've got a spare one). And unless you've
got a sheet called sheet2 and another sheet called sheet 2, you need to be consistent with the spaces. -- David Biddulph "AT" wrote in message ... .... I have used the following to suppress the error msg so nothing comes up, but it fails to correct it. =IF('sheet2!F4="","",'sheet 2'!F3/'sheet2'!F4) So if cell 'sheet2!F4 is blank, then nothing is input, otherwise process F3/F4. To me, it looks like a simple process, but Excel doesnt like it. If someone could shine some light on this, that would be much appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - 3 Rules - Excel 2000
Thanks for the quick replies. Regarding the first reply from Max, I am unsure
where I would apply this. Would this be in the conditional formatting criteria or is it to replace the formula that I posted up originally? Anyway, I feel that I may have misrepresented my problem. The link is between 2 sheets, so sheet 1 is where the formula is applied, calculating/retrieving data from Sheet 2. And finally, the critera for the conditional formatting to occur are linked to cells which (as an example) contain the numbers 90 (Cell A1), 60 (Cell A2) and 1 (Cell A3) (so that it's easy to adjust the variables). From here, I setup the conditional formatting rules to state Turn Green if Cell value is same or greater than Cell A1 Turn Yellow if Cell value Between A1 and A2 Turn Red if Cell value is Between A2 and A3 When I use the formula I mentioned in the first post, if there are numbers in the cells it is pointing to (on a different sheet), after the simple calculation, the cell will chage colour, corresponding to the value i.e. 49, so the cell turns Red. However, when the cell that it is pointing to has no value/entry, then it automatically changes Green rather than remain blank/colourless. I hope this makes it slightly clearer. Thanks for both individuals who have made posts, I really appreciate it. "David Biddulph" wrote: You're missing a quote mark (or you've got a spare one). And unless you've got a sheet called sheet2 and another sheet called sheet 2, you need to be consistent with the spaces. -- David Biddulph "AT" wrote in message ... .... I have used the following to suppress the error msg so nothing comes up, but it fails to correct it. =IF('sheet2!F4="","",'sheet 2'!F3/'sheet2'!F4) So if cell 'sheet2!F4 is blank, then nothing is input, otherwise process F3/F4. To me, it looks like a simple process, but Excel doesnt like it. If someone could shine some light on this, that would be much appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - 3 Rules - Excel 2000
Would this be in the conditional formatting criteria
or is it to replace the formula that I posted up originally? The earlier suggestion's for use in the conditional formatting, via the "Formula Is" option With your further clarification, try it along these lines In your sheet 2, Assume you have the CF "limits" entered in A1: 90 in A2: 60 in A3: 1 Then assume you want to conditionally format col C Select col C (with C1 active) Apply conditional formatting using the "Formula Is" option Condition 1, Formula Is: =AND(ISNUMBER(C1),C1=$A$3,C1<$A$2) Format: Red fill Condition 2, Formula Is: =AND(ISNUMBER(C1),C1=$A$2,C1<$A$1) Format: Yellow fill Condition 3, Formula Is: =AND(ISNUMBER(C1),C1=$A$1) Format: Green fill Click to OK out The above is a more robust way to apply the CF, and should clear up your issue -- Max Singapore http://savefile.com/projects/236895 Downloads: 15,500, Files: 352, Subscribers: 53 xdemechanik --- "AT" wrote: Thanks for the quick replies. Regarding the first reply from Max, I am unsure where I would apply this. Would this be in the conditional formatting criteria or is it to replace the formula that I posted up originally? Anyway, I feel that I may have misrepresented my problem. The link is between 2 sheets, so sheet 1 is where the formula is applied, calculating/retrieving data from Sheet 2. And finally, the critera for the conditional formatting to occur are linked to cells which (as an example) contain the numbers 90 (Cell A1), 60 (Cell A2) and 1 (Cell A3) (so that it's easy to adjust the variables). From here, I setup the conditional formatting rules to state Turn Green if Cell value is same or greater than Cell A1 Turn Yellow if Cell value Between A1 and A2 Turn Red if Cell value is Between A2 and A3 When I use the formula I mentioned in the first post, if there are numbers in the cells it is pointing to (on a different sheet), after the simple calculation, the cell will chage colour, corresponding to the value i.e. 49, so the cell turns Red. However, when the cell that it is pointing to has no value/entry, then it automatically changes Green rather than remain blank/colourless. I hope this makes it slightly clearer. Thanks for both individuals who have made posts, I really appreciate it. "David Biddulph" wrote: You're missing a quote mark (or you've got a spare one). And unless you've got a sheet called sheet2 and another sheet called sheet 2, you need to be consistent with the spaces. -- David Biddulph "AT" wrote in message ... .... I have used the following to suppress the error msg so nothing comes up, but it fails to correct it. =IF('sheet2!F4="","",'sheet 2'!F3/'sheet2'!F4) So if cell 'sheet2!F4 is blank, then nothing is input, otherwise process F3/F4. To me, it looks like a simple process, but Excel doesnt like it. If someone could shine some light on this, that would be much appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - 3 Rules - Excel 2000
Thanks Max, works a treat.
Much appreciated! AT "Max" wrote: Would this be in the conditional formatting criteria or is it to replace the formula that I posted up originally? The earlier suggestion's for use in the conditional formatting, via the "Formula Is" option With your further clarification, try it along these lines In your sheet 2, Assume you have the CF "limits" entered in A1: 90 in A2: 60 in A3: 1 Then assume you want to conditionally format col C Select col C (with C1 active) Apply conditional formatting using the "Formula Is" option Condition 1, Formula Is: =AND(ISNUMBER(C1),C1=$A$3,C1<$A$2) Format: Red fill Condition 2, Formula Is: =AND(ISNUMBER(C1),C1=$A$2,C1<$A$1) Format: Yellow fill Condition 3, Formula Is: =AND(ISNUMBER(C1),C1=$A$1) Format: Green fill Click to OK out The above is a more robust way to apply the CF, and should clear up your issue -- Max Singapore http://savefile.com/projects/236895 Downloads: 15,500, Files: 352, Subscribers: 53 xdemechanik --- "AT" wrote: Thanks for the quick replies. Regarding the first reply from Max, I am unsure where I would apply this. Would this be in the conditional formatting criteria or is it to replace the formula that I posted up originally? Anyway, I feel that I may have misrepresented my problem. The link is between 2 sheets, so sheet 1 is where the formula is applied, calculating/retrieving data from Sheet 2. And finally, the critera for the conditional formatting to occur are linked to cells which (as an example) contain the numbers 90 (Cell A1), 60 (Cell A2) and 1 (Cell A3) (so that it's easy to adjust the variables). From here, I setup the conditional formatting rules to state Turn Green if Cell value is same or greater than Cell A1 Turn Yellow if Cell value Between A1 and A2 Turn Red if Cell value is Between A2 and A3 When I use the formula I mentioned in the first post, if there are numbers in the cells it is pointing to (on a different sheet), after the simple calculation, the cell will chage colour, corresponding to the value i.e. 49, so the cell turns Red. However, when the cell that it is pointing to has no value/entry, then it automatically changes Green rather than remain blank/colourless. I hope this makes it slightly clearer. Thanks for both individuals who have made posts, I really appreciate it. "David Biddulph" wrote: You're missing a quote mark (or you've got a spare one). And unless you've got a sheet called sheet2 and another sheet called sheet 2, you need to be consistent with the spaces. -- David Biddulph "AT" wrote in message ... .... I have used the following to suppress the error msg so nothing comes up, but it fails to correct it. =IF('sheet2!F4="","",'sheet 2'!F3/'sheet2'!F4) So if cell 'sheet2!F4 is blank, then nothing is input, otherwise process F3/F4. To me, it looks like a simple process, but Excel doesnt like it. If someone could shine some light on this, that would be much appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - 3 Rules - Excel 2000
Welcome, glad to hear that.
-- Max Singapore http://savefile.com/projects/236895 Downloads: 15,500, Files: 352, Subscribers: 53 xdemechanik --- "AT" wrote in message ... Thanks Max, works a treat. Much appreciated! AT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create 5 Conditional Formatting rules in Excel 2003? | Excel Discussion (Misc queries) | |||
Conditional Formatting Rules Manager Excel 2007 | Excel Worksheet Functions | |||
Suggestion: Excel 2007- New Conditional Formatting Rules and Optio | Excel Discussion (Misc queries) | |||
More than 3 rules of conditional formatting | Excel Discussion (Misc queries) | |||
I need 5 conditional formatting rules-excel only allows 3 | Excel Discussion (Misc queries) |