title: Google Sheets data validation dropdown filter based on criteria
body: <p>I have a sheet with the Training Participants and the Trainers. Trainers have to be retrained every year and their training date, the corresponding fiscal year (FY), and specialty-trained (IRT) is noted on the Trainers tab. On the Training Participant tab, the training participant records are added. To ensure that only eligible trainers are facilitating training, I am trying to add a data validation dropdown that only lists the trainers trained for the FY of the training date (Col E of the Training Participants sheet) and, if the training type is IRT, only trainers that are trained for that FY AND marked as TRUE for IRT (Col E) on the Training page.</p>
  <p>For example, if I was entering training participants for an IST training that occurred 5/15/22, Amy Peterson, Stan Roberts, and Josh Smith would populate in the dropdown as they are eligible trainers for FY 21/22.</p>
  <p>If I was entering training participants for an IRT training done on 7/1/22 only Amy Peterson and Gabe Winters would populate in the dropdown as those are the only two IRT trainers eligible for FY 22/23.</p>
  <p>I know it would be easier to create a new sheet or tab for every fiscal year, but I would like to keep all of the records for each FY in these two sheets for easy reference.</p>
  <p>Training Participants Sheet:
  <a href="https://i.stack.imgur.com/YuCAf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YuCAf.png" alt="enter image description here" /></a></p>
  <p>Trainers Sheet:
  <a href="https://i.stack.imgur.com/ZMnhF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZMnhF.png" alt="enter image description here" /></a></p>
  <p>Here is my practice sheet which includes a small sample of the data I am working with: <a href="https://docs.google.com/spreadsheets/d/1rORXj5Ifgqn2RWD7GgsEy_QrUcjnz1Zd9jiUlEHxaEU/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1rORXj5Ifgqn2RWD7GgsEy_QrUcjnz1Zd9jiUlEHxaEU/edit?usp=sharing</a></p>