Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating based on 3 Conditions
I'm sure its been covered before, but have been searching to no avail.
I have a list of clients/jobs. each row contains a column of data of date, job address, invoice date, cost. I also have 3 columns that have a drop down choice of yes or no. What i would like is if each of these selections is 'No' then row text is fromated to Red, if columns 1 & 2 = 'No' & col 3 = 'Yes' the row text is Blue, and if all three cols = 'Yes' then all text in that row becomes Green. The main purpose of this is just to give a visual representation on the spreadsheet at a quick glance. Thanks in advance for the help i'm sure i will get. Have a good day/night Moggy -- You realise how little you know when you come to this site |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating based on 3 Conditions
Assuming your three drop-down boxes were in columns D, E and F you could set
up the 3 conditions as follows (just substitue D, E and F for your three columns): Condition 1 Formula Is =AND(IF($D2="No",1,0),IF($E2="No",1,0),IF($F2="No" ,1,0)) Set format to Red text Condition 2 Formula Is =AND(IF($D2="No",1,0),IF($E2="No",1,0),IF($F2="Yes ",1,0)) Set format to Blue text Condition 3 Formula Is =AND(IF($D2="Yes",1,0),IF($E2="Yes",1,0),IF($F2="Y es",1,0)) Set format to Green text You should be able to use the format painter to copy this conditional formatting to all required cells. Of course combinations like Yes, No, Yes will stay black. Andrea Jones www.stratatraining.co.uk "Moggy" wrote: I'm sure its been covered before, but have been searching to no avail. I have a list of clients/jobs. each row contains a column of data of date, job address, invoice date, cost. I also have 3 columns that have a drop down choice of yes or no. What i would like is if each of these selections is 'No' then row text is fromated to Red, if columns 1 & 2 = 'No' & col 3 = 'Yes' the row text is Blue, and if all three cols = 'Yes' then all text in that row becomes Green. The main purpose of this is just to give a visual representation on the spreadsheet at a quick glance. Thanks in advance for the help i'm sure i will get. Have a good day/night Moggy -- You realise how little you know when you come to this site |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating based on 3 Conditions
Thanks Andrea,
Worked Great. Have a good day -- You realise how little you know when you come to this site "Andrea Jones" wrote: Assuming your three drop-down boxes were in columns D, E and F you could set up the 3 conditions as follows (just substitue D, E and F for your three columns): Condition 1 Formula Is =AND(IF($D2="No",1,0),IF($E2="No",1,0),IF($F2="No" ,1,0)) Set format to Red text Condition 2 Formula Is =AND(IF($D2="No",1,0),IF($E2="No",1,0),IF($F2="Yes ",1,0)) Set format to Blue text Condition 3 Formula Is =AND(IF($D2="Yes",1,0),IF($E2="Yes",1,0),IF($F2="Y es",1,0)) Set format to Green text You should be able to use the format painter to copy this conditional formatting to all required cells. Of course combinations like Yes, No, Yes will stay black. Andrea Jones www.stratatraining.co.uk "Moggy" wrote: I'm sure its been covered before, but have been searching to no avail. I have a list of clients/jobs. each row contains a column of data of date, job address, invoice date, cost. I also have 3 columns that have a drop down choice of yes or no. What i would like is if each of these selections is 'No' then row text is fromated to Red, if columns 1 & 2 = 'No' & col 3 = 'Yes' the row text is Blue, and if all three cols = 'Yes' then all text in that row becomes Green. The main purpose of this is just to give a visual representation on the spreadsheet at a quick glance. Thanks in advance for the help i'm sure i will get. Have a good day/night Moggy -- You realise how little you know when you come to this site |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating with 3 conditions | Excel Discussion (Misc queries) | |||
Conditional Formating Based on Date | Excel Discussion (Misc queries) | |||
Can I use more than three conditions for conditional formating? | Excel Worksheet Functions | |||
More that 3 conditions for conditional formating? | New Users to Excel | |||
Conditional Formating 4 conditions | Excel Discussion (Misc queries) |