Hello Forum. Police officers have to qualify twice a year at the gun range. There are 3 dates to choose from in the spring and 3 dates in the fall. Based on seniority/rank, I want to create a formula that will select the officer's first choice if the target number for that range date is not already met. I have a table with officer badge numbers and names with 6 columns next to it with their 1st, 2nd, 3rd choice for spring and 1st, 2nd, and 3rd choice for fall. Columns are labeled Spring1, Spring2, Spring3, Fall1, Fall2, Fall3. I want a results table that points to a variable called "Max Officers per Range Date". Results table should have a Roster sorted by Badge number with a Spring and Fall column next to it to show what date was selected for them.
I've talked out the formula in hopes someone can organize my thoughts into a proper formula. Here it goes:
Lookup the first choice for the first 20 highest ranking officers (Max officer variable), if the first choice of the next highest ranking officer already has 20 listed, and if their second choice does not already have 20 officers listed, lookup their second choice, if not, give them their third choice.
Range dates are March 1, April 1, May 1, Sept 1, Oct 1, and Nov 1.
Any help would be appreciated.
I've talked out the formula in hopes someone can organize my thoughts into a proper formula. Here it goes:
Lookup the first choice for the first 20 highest ranking officers (Max officer variable), if the first choice of the next highest ranking officer already has 20 listed, and if their second choice does not already have 20 officers listed, lookup their second choice, if not, give them their third choice.
Range dates are March 1, April 1, May 1, Sept 1, Oct 1, and Nov 1.
Any help would be appreciated.
Last edited:
