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

Solved/Closed
Mounta1n Posts 8 Registration date Monday July 29, 2013 Status Member Last seen August 5, 2013 - Aug 2, 2013 at 07:47 AM
Mounta1n Posts 8 Registration date Monday July 29, 2013 Status Member Last seen August 5, 2013 - Aug 5, 2013 at 09:47 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 3, 2013 at 01:22 AM
is there any LOGIC in the values ats the interesection of rows and columns ????
0
Mounta1n Posts 8 Registration date Monday July 29, 2013 Status Member Last seen August 5, 2013
Aug 5, 2013 at 04:44 AM
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
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 5, 2013 at 05:59 AM
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
0
Mounta1n Posts 8 Registration date Monday July 29, 2013 Status Member Last seen August 5, 2013
Aug 5, 2013 at 06:14 AM
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!
0

Didn't find the answer you are looking for?

Ask a question
Mounta1n Posts 8 Registration date Monday July 29, 2013 Status Member Last seen August 5, 2013
Aug 5, 2013 at 06:40 AM
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
0
Mounta1n Posts 8 Registration date Monday July 29, 2013 Status Member Last seen August 5, 2013
Aug 5, 2013 at 09:47 AM
=+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"))))))))))
0