Related:

- Conditional formatting can't caputure text...NEED VB CODE PLEASE
- Conditional formatting if cell does not contain specific text ✓ - Forum - Excel
- Conditional formatting if another cell contains any text ✓ - Forum - Excel
- Excel conditional formatting 5 color scale ✓ - Forum - Excel
- Copy conditional formatting from one row to another ✓ - Forum - Excel
- Excel conditional formatting if cell contains any date - How-To - Excel

## 6 replies

venkat1926

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

is there any LOGIC in the values ats the interesection of rows and columns ????

Mounta1n

- Posts
- 8
- Registration date
- Monday July 29, 2013
- Status
- Member
- Last seen
- August 5, 2013

Hi

Yes indeed. the logic is set out by associating the possibilities with each of the three outcomes. You can substitute what you want in for the various conditions.

I think i have worked out a way to do this though.

By using nested IF statements and the AND function:

=IF(AND(A1="1",B1="A"),"x",IF(AND(A1="5",B1=A),"y" etc......

Will test it today. Thanks

Yes indeed. the logic is set out by associating the possibilities with each of the three outcomes. You can substitute what you want in for the various conditions.

I think i have worked out a way to do this though.

By using nested IF statements and the AND function:

=IF(AND(A1="1",B1="A"),"x",IF(AND(A1="5",B1=A),"y" etc......

Will test it today. Thanks

venkat1926

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

can be done but you shd have 29 nests . if your versions 2003 or before you cannot have more tahn 7 nests. xll 2007 you havae more

if it is such an elaborate formula then just tpe nnext to each combinaton x or y or z

if it is such an elaborate formula then just tpe nnext to each combinaton x or y or z

Mounta1n

- Posts
- 8
- Registration date
- Monday July 29, 2013
- Status
- Member
- Last seen
- August 5, 2013

I believe you can define names for each of the 7 nests which means you can have n nests?

What do you mean by this line?

"if it is such an elaborate formula then just tpe nnext to each combinaton x or y or z"

Have to say i am struggling with this!

What do you mean by this line?

"if it is such an elaborate formula then just tpe nnext to each combinaton x or y or z"

Have to say i am struggling with this!

Mounta1n

- Posts
- 8
- Registration date
- Monday July 29, 2013
- Status
- Member
- Last seen
- August 5, 2013

Ok I think we are close but i think the issue i am having is a limitation of the AND function. My conditions are based off of text NOT integers. i.e

EG:

So what i need is:

IF CELL A1 = "0 - 250k" and CELL B1 = "< 30 days", do not escalate

IF CELL A1 = "500k -1,000k" and CELL B1 = "< 30 days" do not escalate

IF CELL A1 = ">1000k" and CELL B1 = "< 30 days" do not escalate

IF CELL A1 ="0 -250k" and CELL B1 = "30-60 days" do not escalate

IF CELL A1 = "500-1000k" and CELL B1 = "30-60 days" escalate to md

IF CELL A1 = ">1000k" and CELL B1 = "30-60 days" escalate to CEO

IF CELL A1 = "0 - 250k" and CELL B1 = " > 60 days" escalate to md

IF CELL A1 = "500-1000k" and CELL B1 = "> 60 days" escalate to CEO

IF CELL A1 = ">1000k" and CELL B1 = "> 60 days" escalate to CEO

EG:

So what i need is:

IF CELL A1 = "0 - 250k" and CELL B1 = "< 30 days", do not escalate

IF CELL A1 = "500k -1,000k" and CELL B1 = "< 30 days" do not escalate

IF CELL A1 = ">1000k" and CELL B1 = "< 30 days" do not escalate

IF CELL A1 ="0 -250k" and CELL B1 = "30-60 days" do not escalate

IF CELL A1 = "500-1000k" and CELL B1 = "30-60 days" escalate to md

IF CELL A1 = ">1000k" and CELL B1 = "30-60 days" escalate to CEO

IF CELL A1 = "0 - 250k" and CELL B1 = " > 60 days" escalate to md

IF CELL A1 = "500-1000k" and CELL B1 = "> 60 days" escalate to CEO

IF CELL A1 = ">1000k" and CELL B1 = "> 60 days" escalate to CEO

Mounta1n

- Posts
- 8
- Registration date
- Monday July 29, 2013
- Status
- Member
- Last seen
- August 5, 2013

=+IF(AND(U5="0 - 250k",V5="< 30 DAYS"),"No escalation",IF(AND(U5="250k - 1,000k",V5="< 30 DAYS"),"No escalation",IF(AND(U5="> 1,000k",V5="< 30 DAYS"),"No escalation",IF(AND(U5="0 - 250k",V5="30 - 60 DAYS"),"No escalation",IF(AND(U5="250k - 1,000k",V5="30 - 60 DAYS"),"Regional CFO/Head Fin Ops",IF(AND(U5="> 1,000k",V5="30 - 60 DAYS"),"Regional CFO/Head Fin Ops",IF(AND(U5="0 - 250k",V5="30 - 60 DAYS"),"Reinsurance CFO",IF(AND(U5="0 - 250k",V5="30 - 60 DAYS"),"Do not escalate",IF(AND(U5="> 1,000k",V5="> 60 DAYS"),"Reinsurance CFO",IF(AND(U5="<Enter amount>",V5="<Enter amount>"),"No escalation","No escalation"))))))))))