Using IF with multiple sets of conditional formatting rules?

[newmank]

I have a spreadsheet of data that I would like to use conditional formatting with...essentially colour coding the cell based upon a range of data. That is easy. However...I have two sets of condition rules that I would like excel to choose from based upon data in another cell.

For example...we have data from the Fall and from the Spring. We have a set of Fall Conditions and a set of Spring Conditions. I want to have excel check Column C for either 'Fall' or 'Spring' and apply the set of conditions that are applicable. (Each set contains for ranges and assigned colour coding for the cells).

Doable?

Thank you.

 

[newmank]

Thinking this through a bit more...It may be simpler to create the if statement so that if 'Fall' is present it does one set of conditional formatting and if it isn't present it does another.

 

[Snakehips]

newmank,

If the cells are to be formatted same colour for Fall and Spring then you could use like
=IF($C1="Fall",$V$1="x",$W$1="x")
where e.g. $V$1="x" is the test for Fall and $W$1="x" is the test for not Fall i.e. Spring.


Otherwise set up two rules with your current conditions as an element of an AND() along with the test for season eg

eg. =AND($C1 ="Fall", $V1 = "x")

Hope that helps.

 

[newmank]

It begins to...the catch is...there would be multiple possible entries.

So in Column C we would define Spring or Fall. Then in Columns D to W we would enter a numeric value.

I would want the If statement to check for Spring or Fall...then apply a colour based upon a range...for example...0 to 9 is red, 10-14 is Yellow, 15-19 is green and 20-24 is Blue. But for the Spring the ranges change. 0 to 11 is red, 12-16 is yellow, 17-22 is Green, and 20-23 is blue.

I have already applied conditional formatting...but want to change the conditions of the formatting depending on what is noted for Spring or Fall.

Here is a link to a screen shot of the spreadsheet: https://drive.google.com/file/d/0BwiNsV1CBeiTUHFfRmttWnRNZTg/view?usp=sharing

 

[Snakehips]

Ok, I'm assuming that you currently have 4 CF rules set for each column, based on score cell values being within one of the four defined ranges for that column???

If so then you will still need four rules per column (unless some columns share common ranges) and will need to apply a Custom CF formula for each.
I think '17-22 is Green and 20-23 is Blue ' above, is a typo ?? so I have assumed 17-22 and 23-24 for Spring in my example below for column D

So, again, assuming score data starts in row 12 then maybe try...
Sheet2

*	C	D
12	Spring	20
13	Fall	20
14	Fall	11
15	Spring	11

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format D12 1. / Formula is =IF(OR($C12="",$D12=""),FALSE,IF($C12="Fall",AND($D12>=20,$D12<25),AND($D12>=23,$D12<25))) Abc D12 2. / Formula is =IF(OR($C12="",$D12=""),FALSE,IF($C12="Fall",AND($D12>=15,$D12<20),AND($D12>=17,$D12<23))) Abc D12 3. / Formula is =IF(OR($C12="",$D12=""),FALSE,IF($C12="Fall",AND($D12>=10,$D12<15),AND($D12>=12,$D12<17))) Abc D12 4. / Formula is =IF(OR($C12="",$D12=""),FALSE,IF($C12="Fall",AND($D12>=0,$D12<10),AND($D12>=0,$D12<12))) Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Hope that helps.

 

[newmank]

Yes...Helped perfectly! Have it working exactly how I need it now!

Thank you!!!

 

[newmank]

Ok...maybe I just missed a step...or have just made this more difficult or added a step that I didn't include in my initial description.

Each colour has two ranges...one for Fall and one for Spring. I want the conditional formatting to check for 'Spring' or 'Fall' then apply the proper range.

For example...


For Letter Identification...I want it to check for Fall or Spring. If it is fall then 0-9 = Red, 10-14=yellow, 15-19 = Green and 20-24=blue. But...if it is Spring then 0-11 is red, 12-16 is yellow, 17-22 is green and 23-24 is Blue (the image below says 20-24...but should be 23-24). There are about 150 records in each column...and I get the formula would need to be different for each column depending on the different ranges.

I have attached a screenshot to show the spring/fall ranges.

https://drive.google.com/file/d/0BwiNsV1CBeiTRzhWVXlrQmlqdUk/view?usp=sharing


Thank you again.

Kevin