I'm trying to create a spreadsheet to help with training resource. It's based on the type of training, the specific training course, the location of the training and the trainer who can deliver it.
I've got 4 fields at the minute. Type - which is a simple drop down list. Course - which is a dependent drop down based on the Type selected. Location which is a drop down list. Trainer - only certain trainers can deliver particular courses so this is a drop down based on the Type. The formula I've used for the trainer drop down is : =IFERROR(INDEX('Raw Data'!$B$2:$B$49, SMALL(IF($B$1='Raw Data'!$A$2:$A$49, MATCH(ROW('Raw Data'!$A$2:$A$49), ROW('Raw Data'!$A$2:$A$49)), ""), ROW(A1))), "")
Raw data column A is Type, column B is Trainer.
So far so good, this formula is working BUT I need an exclusion - certain trainers have said they will only work at certain locations. This data is held in column C on the raw data sheet. How can I change my formula so that the trainer drop down will only show the trainers qualified to deliver the type of course if it's at a location they've said they're happy to work from? I've tried this formula but no joy : =IF(B3="", FILTER('Raw Data'!B2:B49,'Raw Data'!A2:A49=B1),FILTER('Raw Data'!B2:B49,'Raw Data'!A2:A49=B1,'Raw Data'!C2:C49<>B3))
I've got 4 fields at the minute. Type - which is a simple drop down list. Course - which is a dependent drop down based on the Type selected. Location which is a drop down list. Trainer - only certain trainers can deliver particular courses so this is a drop down based on the Type. The formula I've used for the trainer drop down is : =IFERROR(INDEX('Raw Data'!$B$2:$B$49, SMALL(IF($B$1='Raw Data'!$A$2:$A$49, MATCH(ROW('Raw Data'!$A$2:$A$49), ROW('Raw Data'!$A$2:$A$49)), ""), ROW(A1))), "")
Raw data column A is Type, column B is Trainer.
So far so good, this formula is working BUT I need an exclusion - certain trainers have said they will only work at certain locations. This data is held in column C on the raw data sheet. How can I change my formula so that the trainer drop down will only show the trainers qualified to deliver the type of course if it's at a location they've said they're happy to work from? I've tried this formula but no joy : =IF(B3="", FILTER('Raw Data'!B2:B49,'Raw Data'!A2:A49=B1),FILTER('Raw Data'!B2:B49,'Raw Data'!A2:A49=B1,'Raw Data'!C2:C49<>B3))
