Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating
I am using Excel 2007 and would like to create certain rules and cannot
figure out how to do using conditional format. Lets say I have a typical spreadsheet. Here is the example I am looking for. Lets say A4 needs to have a certain fill color if in J4 through n4 contains the words "help" twice. Then I need A4 to change its fill color if J4 through N4 contain the word "done" once. I have tried several things but cannot figure out. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating
Try CF formulae of
=COUNTIF(A4:J4,"help")=2 and =COUNTIF(J4:N4,"done")=2 -- __________________________________ HTH Bob "Brent" wrote in message ... I am using Excel 2007 and would like to create certain rules and cannot figure out how to do using conditional format. Lets say I have a typical spreadsheet. Here is the example I am looking for. Lets say A4 needs to have a certain fill color if in J4 through n4 contains the words "help" twice. Then I need A4 to change its fill color if J4 through N4 contain the word "done" once. I have tried several things but cannot figure out. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating
Select A4 and then call up the Conditional Formatting "New Rule" dialog. You
will need two rules and both of them will use the "Use a formula to determine which cells to format" option in the "Select a Rule Type" listing. These are the two formulas you would use in the "Format values where this formula is true" field (for each rule individually)... =COUNTIF(J4:N4,"done")=1 =COUNTIF(J4:N4,"help")=2 -- Rick (MVP - Excel) "Brent" wrote in message ... I am using Excel 2007 and would like to create certain rules and cannot figure out how to do using conditional format. Lets say I have a typical spreadsheet. Here is the example I am looking for. Lets say A4 needs to have a certain fill color if in J4 through n4 contains the words "help" twice. Then I need A4 to change its fill color if J4 through N4 contain the word "done" once. I have tried several things but cannot figure out. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating
Thanks for the feedback. I got this to work, but I have one more question.
Now I would like a4 to change to a different color if the following is met. row J4 thru N4 has "help" twice and "done" once. Is there a way to combine if both criteria have been met? "Rick Rothstein" wrote: Select A4 and then call up the Conditional Formatting "New Rule" dialog. You will need two rules and both of them will use the "Use a formula to determine which cells to format" option in the "Select a Rule Type" listing. These are the two formulas you would use in the "Format values where this formula is true" field (for each rule individually)... =COUNTIF(J4:N4,"done")=1 =COUNTIF(J4:N4,"help")=2 -- Rick (MVP - Excel) "Brent" wrote in message ... I am using Excel 2007 and would like to create certain rules and cannot figure out how to do using conditional format. Lets say I have a typical spreadsheet. Here is the example I am looking for. Lets say A4 needs to have a certain fill color if in J4 through n4 contains the words "help" twice. Then I need A4 to change its fill color if J4 through N4 contain the word "done" once. I have tried several things but cannot figure out. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating
Is that in addition to the first two I gave you or in place of the first two
I gave you? Also, how many criteria will you ultimately want on that one cell (there are limitations as to how many different conditional formats you can have on a single range). -- Rick (MVP - Excel) "Brent" wrote in message ... Thanks for the feedback. I got this to work, but I have one more question. Now I would like a4 to change to a different color if the following is met. row J4 thru N4 has "help" twice and "done" once. Is there a way to combine if both criteria have been met? "Rick Rothstein" wrote: Select A4 and then call up the Conditional Formatting "New Rule" dialog. You will need two rules and both of them will use the "Use a formula to determine which cells to format" option in the "Select a Rule Type" listing. These are the two formulas you would use in the "Format values where this formula is true" field (for each rule individually)... =COUNTIF(J4:N4,"done")=1 =COUNTIF(J4:N4,"help")=2 -- Rick (MVP - Excel) "Brent" wrote in message ... I am using Excel 2007 and would like to create certain rules and cannot figure out how to do using conditional format. Lets say I have a typical spreadsheet. Here is the example I am looking for. Lets say A4 needs to have a certain fill color if in J4 through n4 contains the words "help" twice. Then I need A4 to change its fill color if J4 through N4 contain the word "done" once. I have tried several things but cannot figure out. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating
This is in addition to the first two. This would be the last criterior. I
am creating a worksheet that lets me know if I have seen a patient twice aka "help" and the doc has seen once "done". I am trying to color corridinate the pt names in col a "Rick Rothstein" wrote: Is that in addition to the first two I gave you or in place of the first two I gave you? Also, how many criteria will you ultimately want on that one cell (there are limitations as to how many different conditional formats you can have on a single range). -- Rick (MVP - Excel) "Brent" wrote in message ... Thanks for the feedback. I got this to work, but I have one more question. Now I would like a4 to change to a different color if the following is met. row J4 thru N4 has "help" twice and "done" once. Is there a way to combine if both criteria have been met? "Rick Rothstein" wrote: Select A4 and then call up the Conditional Formatting "New Rule" dialog. You will need two rules and both of them will use the "Use a formula to determine which cells to format" option in the "Select a Rule Type" listing. These are the two formulas you would use in the "Format values where this formula is true" field (for each rule individually)... =COUNTIF(J4:N4,"done")=1 =COUNTIF(J4:N4,"help")=2 -- Rick (MVP - Excel) "Brent" wrote in message ... I am using Excel 2007 and would like to create certain rules and cannot figure out how to do using conditional format. Lets say I have a typical spreadsheet. Here is the example I am looking for. Lets say A4 needs to have a certain fill color if in J4 through n4 contains the words "help" twice. Then I need A4 to change its fill color if J4 through N4 contain the word "done" once. I have tried several things but cannot figure out. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating
Try this formula for your 3rd rule...
=AND(COUNTIF(J4:N4,"help")=2,COUNTIF(J4:N4,"done") =1) -- Rick (MVP - Excel) "Brent" wrote in message ... This is in addition to the first two. This would be the last criterior. I am creating a worksheet that lets me know if I have seen a patient twice aka "help" and the doc has seen once "done". I am trying to color corridinate the pt names in col a "Rick Rothstein" wrote: Is that in addition to the first two I gave you or in place of the first two I gave you? Also, how many criteria will you ultimately want on that one cell (there are limitations as to how many different conditional formats you can have on a single range). -- Rick (MVP - Excel) "Brent" wrote in message ... Thanks for the feedback. I got this to work, but I have one more question. Now I would like a4 to change to a different color if the following is met. row J4 thru N4 has "help" twice and "done" once. Is there a way to combine if both criteria have been met? "Rick Rothstein" wrote: Select A4 and then call up the Conditional Formatting "New Rule" dialog. You will need two rules and both of them will use the "Use a formula to determine which cells to format" option in the "Select a Rule Type" listing. These are the two formulas you would use in the "Format values where this formula is true" field (for each rule individually)... =COUNTIF(J4:N4,"done")=1 =COUNTIF(J4:N4,"help")=2 -- Rick (MVP - Excel) "Brent" wrote in message ... I am using Excel 2007 and would like to create certain rules and cannot figure out how to do using conditional format. Lets say I have a typical spreadsheet. Here is the example I am looking for. Lets say A4 needs to have a certain fill color if in J4 through n4 contains the words "help" twice. Then I need A4 to change its fill color if J4 through N4 contain the word "done" once. I have tried several things but cannot figure out. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formating | Excel Discussion (Misc queries) | |||
conditional formating | Excel Worksheet Functions | |||
Conditional Formating | Excel Worksheet Functions | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
Conditional formating | Excel Worksheet Functions |