Conditional formatting can't caputure text...NEED VB CODE PLEASESolved/Closed

Question

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

venkat1926 · Answer

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

Mounta1n · Answer

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

venkat1926 · Answer

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

Mounta1n · Answer

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!

Mounta1n · Answer

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

Mounta1n · Answer

=+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="",V5=""),"No escalation","No escalation"))))))))))

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

6 responses

Similar discussions

Subscribe To Our Newsletter!