Conditional formatting can't caputure text...NEED VB CODE PLEASE

[Solved/Closed]
Report
Posts
8
Registration date
Monday July 29, 2013
Status
Member
Last seen
August 5, 2013
-
Posts
8
Registration date
Monday July 29, 2013
Status
Member
Last seen
August 5, 2013
-
Hello,

I understand i can't use c formatting to insert text and therefore am looking for a piece of code that will perform the following.

Consider a matrix.

I have 3 outcomes. Call them x,y,z

Then we have 9 variable running down vertically and 3 variables horizontally.

Let 9 vertical variables be:

1
2
3
4
5
6
7
8
9

For 3 horizontal variables:

A
B
C


For outcome "x" the following conditions are TRUE:

1A
2A
3A
4A
7A
1B
4B


For outcome "y" the following conditions are TRUE:

5A
6A
8A
2B
3B
5B
7B
8B
1C
2C
4C
7C

For outcome "z" the following conditions is TRUE:

9A
6B
9B
3C
5C
6C
8C
9C

I hope this makes sense?

Any help appreciated!

Brendon

6 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
is there any LOGIC in the values ats the interesection of rows and columns ????
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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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
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!
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
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"))))))))))