Data Validation Box Pops Up when cell meets certain criteria

[RUSTYC]

Right now I have cell E4 filled with:
=IF(D4='Training Data'!$A$2,'Training Data'!$B$2,IF(D4='Training Data'!$A$3,'Training Data'!$B$3,IF(D4='Training Data'!$A$4,'Training Data'!$B$4,IF(D4='Training Data'!$A$5,'Training Data'!$B$5,IF(D4='Training Data'!$A$6,'Training Data'!$B$6,IF(D4='Training Data'!$A$7,'Training Data'!$B$7,IF(D4='Training Data'!$A$8,'Training Data'!$B$8,IF(D4='Training Data'!$A$9,'Training Data'!$B$9,IF(D4='Training Data'!$A$10,"PLEASE ENTER NAME OF COLLEGE",IF(D4='Training Data'!$A$11,"PLEASE ENTER NAME OF HOSPITAL",IF(D4='Training Data'!$A$12,'Training Data'!$B$12,IF(D4='Training Data'!$A$13,'Training Data'!$B$13,IF(D4='Training Data'!$A$14,'Training Data'!$B$14,”N/A”)))))))))))))

And it works just as I need it to, however I would like to make this more user friendly by adding a drop down list in place of asking the user to enter the name of the hospital/college. I would like a drop down list to pop up or the cell to activate a data validation list when below is true. Something like this:

IF(D4='Training Data'!$A$10,Activate Data Validation (I know "Activate Data Validation" isn't a function just added it here so you can see).
IF(D4='Training Data'!$A$11,Activate Data Validation,

I have the lists/choices (both hospitals and colleges) defined on another worksheet that would populate the data validation.

Thanks in advance!