nesting IF and AND statements

mcintoshmc

Active Member
Joined
Aug 10, 2007
Messages
277
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?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Couldn't you just remove the last character from what is in L2?

Code:
=IF(AND(F2="yes",L2<>""),LEFT(L2,LEN(L2)-1),"")
 
Upvote 0
Couldn't you just remove the last character from what is in L2?

Code:
=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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Do you always want A1 to have an A in it if A2 is before today's date?

If so, perhaps, in A1:
Code:
=IF(A2<=TODAY(),"A","G")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top