[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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
  • 3
2 Solutions
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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now