nesting IF and AND statements

[mcintoshmc]

Row 1 contains several columns. The ones I'm concerned about are:
F2 and L2. F2 will give you a status of "yes" or "no" if they enrolled or not. L2 will give you their program of interest. It will either say....
PT1, PT3, MB1, MB3, MT1, MT3, MA1, MA3, DA1, DA3, CST1, CST3, BOA1, BOA3.

On the farthest column (N) i want it to say PT, or DA, or whatever program it is, without the number. I can get it to work for just 1 program by using =IF(AND(L2='Starts Chart'!$F$25,F2="yes"),"PT","")
F25 says PT1, F26 will say PT3, and G25 will say MB1. I think you get the picture. What formula can I use to include all programs 1 and 3?

 

[Scott Huish]

Couldn't you just remove the last character from what is in L2?

=IF(AND(F2="yes",L2<>""),LEFT(L2,LEN(L2)-1),"")

 

[mcintoshmc]

you are a life savior!!!!

 

[mcintoshmc]

Couldn't you just remove the last character from what is in L2?

=IF(AND(F2="yes",L2<>""),LEFT(L2,LEN(L2)-1),"")

The formula work perfectly. I don't get it. How did you do it? what is len? What was the left for?

 

[Scott Huish]

LEN returns the length of the string.

So with PT1 LEN returns a length of 3, then we subtract 1, leaving 2. So we take the LEFT 2 characters of this string which is PT.

With BOA1, LEN returns a length of 4 then we subtract 1, leaving 3. So we take the LEFT 3 characters of this string which is BOA.

 

[mcintoshmc]

thats pretty good. By any chance do you know a function that will automatically change a cell from a to g once a specific date has passed? For example: cell A1 says "A" and cell A2 says "8/24/07" Is there any way to have cell A1 automatically change to G once 8/24 hits?

 

[Scott Huish]

Do you always want A1 to have an A in it if A2 is before today's date?

If so, perhaps, in A1:

=IF(A2<=TODAY(),"A","G")

 

[mcintoshmc]

I want A1 to always have an "A" in it till A2's date has passed. If A2's date is 6 months from now, I want the cell to say "A" until then. The formula you provided will turn "A" to "G" after todays date has passed even though their grad date isn't until December for example.

 

[Scott Huish]

=IF(A2>TODAY(),"A","g")

 

[mcintoshmc]

your amazing!!