我在培训参与者和培训师上有一张床单。在“培训师”选项卡上注明了培训师每年及其培训日期，相应的会计年度（FY）和专业培训（IRT）的培训日期。在培训参与者选项卡上，添加了培训参与者记录。为了确保只有合格的培训师正在促进培训，我正在尝试添加数据验证下拉列表，该下拉列表仅列出培训培训日期培训的培训日期（培训参与者表的Col E），如果培训类型是IRT，则仅在培训页面上为IRT（Col e）而受过培训的培训师。
例如，如果我正在参加5/15/22的IST培训培训，那么艾米·彼得森（Amy Peterson），斯坦·罗伯茨（Stan Roberts）和乔什·史密斯（Josh Smith）将在下拉阶段填充，因为他们是21/22财政年度的合格培训师。
如果我要参加在7/1/22进行的IRT培训的培训参与者，只有艾米·彼得森（Amy Peterson）和加贝·温特斯（Gabe Winters）才会在下拉阶段填充，因为这是唯一有资格获得22/23财年的IRT培训师。
我知道，在每个会计年度，创建一个新的表或选项卡会更容易，但是我想保留这两张床单中每个FY的所有记录，以便于参考。
培训参与者表：
培训师表：
这是我的练习表，其中包括我正在使用的一小部分数据： https://docs.google.com/spreadsheets/d/1rorxj5ifgqn2rwd7ggsey_qrucjnz1zd9jiulehxaeu/edit?usp = sharing
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.
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.
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.
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.
Training Participants Sheet:
Trainers Sheet:
Here is my practice sheet which includes a small sample of the data I am working with: https://docs.google.com/spreadsheets/d/1rORXj5Ifgqn2RWD7GgsEy_QrUcjnz1Zd9jiUlEHxaEU/edit?usp=sharing
发布评论