I'm trying to create a formula that auto-populates colum that meets a spefic criteria

COndition 1: (D3):I'm trying to figure a formula or way if the rank is AB or AMN or A1C or SRA or SSGT or TSGT then return "AF Form 931", If not return "AF Form 932"
  My attempt:  =IF(B3=AB,AF Form 931,IF(B3=AMN,AF Form 931,IF(B3=A1C,AF Form 931, AF Form 931)))

Condition 2: (G3)I'm trying to figure out how if status is equal to 1, or 2 and rank equals a specific rank midterm will equal a specific dates

Rank            Initial ACA Closeout      Midterm ACA Closeout
CMSgt             N/A                  31 Jan 2015
SMSgt             31 Jul 2014            31 Jul 2015
MSgt             N/A                  30 Sep 2014
TSgt             30 Nov 2014            30 Nov 2015
SSgt             31 Jan 2015            31 Jan 2016
SrA             N/A                  31 Mar 2015

If the status equals 3 and rank equals a specific rank midterm will equal a specific date:
Rank            Midterm ACA Closeout
CMSgt             31 Jan 2015
SMSgt             31 Mar 2015
MSgt             31 May 2015
TSgt             31 Jul 2015
SSgt             30 Sep 2015
SrA             30 Nov 2015
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Condition 1:  Insert this formula in cell D3 and copy down:
=IF(OR(A3="AB",A3="AMN",A3="A1C",A3="SRA",A3="SSGT",A3="TSGT"),"AF Form 931","AF Form 932")

Your example above listed B3 as the test, but your description says that you want to test the "rank" which is in column A.

Alternatively, since you have a master list of valid ranks on the Data sheet and your test values are in cells C1:C6, you could use this formula:
=IF(ISERROR(VLOOKUP(A3,Data!$C$1:$C$6,1,FALSE)),"AF Form 932","AF Form 931")

I'll address your second condition separately.

Glenn RayExcel VBA DeveloperCommented:
I have a few questions about Condition 2:  
1) Do you want the formula that determines the default midterm to be in column H (starting in H3).  You list cell G3, but that is for the "Initial ACA Closeout Date."
2) Do you only want to see the default "Midterm ACA Closeout Date" for status = 1 or 2, because you include some values in your example above for the "Initial ACA Closeout Date"
3) What should be the default Midterm dates be for the other three possible ranks (AB, AMC, A1C)?

I have a likely solution, but need to you clarify these questions before presenting it.

cfhasan1Author Commented:
1) Yes, my mistake
2)  If status=3 then,
If the status equals 3 and rank equals a specific rank midterm will equal a specific date:
 Rank            Midterm ACA Closeout
 CMSgt             31 Jan 2015
 SMSgt             31 Mar 2015
 MSgt             31 May 2015
 TSgt             31 Jul 2015
 SSgt             30 Sep 2015
 SrA             30 Nov 2015

3) N/A
Glenn RayExcel VBA DeveloperCommented:
Here's my solution for condition 2:
1a) Add a table for the default closeout dates for each possible rank and status.  This table would be added on a separate sheet, named appropriately "Closeout Dates".
1b) Insert the default dates as per your specifications above.  For the three other ranks (AB, AMN, A1C), intentionally insert a formula (=1/0) to produce an error (#DIV/0!).  That's needed for the next formula.
2) Insert the following formula in cell H3 and copy down:
=IFERROR(SUMIFS('Closeout Dates'!$C$2:$C$28,'Closeout Dates'!$A$2:$A$28,C3,'Closeout Dates'!$B$2:$B$28,A3),"N/A")
Because there are nine ranks and three status levels, that makes 27 possible combinations (rows 2:28).
3) Change the formulas for G3 and I3 and copy down:
G3: =IF(H3<>"N/A",H3-30,"N/A")
I3: =IF(H3<>"N/A",H3+30,"N/A")

I've attached a modified workbook for you to test.  Let me know if you have any questions.


PS  I've also added Data Validation to the test cells in column A to reference the list of ranks on the "Data" sheet.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.