I have a spreadsheet that I used to track if members have required training at specific levels
A:1 - Name B:1 - Submission status C:1 - Certificate obtained D:1 - Required Certificate level E:1 - Compliance Status
B - Can be awaiting or submitted C - Name of the certificate D - There are 14 required certificate levels and each contains several certificates that a member can obtain (placed in named ranges on another sheet) E - Will be one of a few options: Compliant, Non-Compliant; needs submission, or Compliant; does not meet required level
I would like a formula or VB that can determine if the member's certificate meets the requirement of the level for the position they occupy. If a member has the certificate or one of a higher level then they are compliant.
I want the formula or code to determine which range to start parsing through based on the required level first then move through the remaining higher levels until it finds a match or it returns a false
ie: Level I certs include: CERT A & CERT B/Level II certs include: CERT B, CERT C, CERT D
Member Jane Doe occupies a level I position and he has obtained CERT C, so she would be compliant.
Member John Doe occupies a level II position and he has obtained CERT A, so he would not be compliant because his position requires a higher level certification
Bookmarks