Creating Alerts for Non-matches

I am continuing my quest to develop a timetable on Excel.......

Students choose different electives, upto a maximum of 20. In the example below, I have shown them with only four electives. Some choices require a pre-requisite subject to be chosen. In the example below, let us assume that you must select WXY101 in your choices if you want to include WXY102 as an option as well. Jane Doe fits this criteria, so there is no alert.

However, John Smith is trying to complete WXY102 without having chosen WXY101, and hence WXY102 appears in the alert column as a problem selection. Note that choosing WXY101 without choosing WXY102 is OK, as you can just complete a pre-requisite subject.


Obviously I have manually typed the subject code in the alert column - is there a way to automate this via a formula?

Any help would be greatly appreciated!
Cheers
Paul

Please attach an actual worksheet instead... Excel Jeanie doesn't work here.

I wonder if it is better to highlight the "bad" choices instead of showing separate alerts, in case there are multiple "bad" choices, it is easier to highlight than to list them

See attached.

First create a listing of all courses and their corresponding prerequisites (if there are no prerequisites, then list the course and relist it as its own prerequisite).

Then apply conditional formatting by selecting entire range C2:F3 and go to the Home menu and selecting Conditional Formatting >> New Rule >> Use a formula to determine which cells to format.. then apply formula:

=ISNA(MATCH(INDEX($M$2:$M$7,MATCH(C2,$L$2:$L$7,0)),$C2:$F2,0))

Click Format and choose from the Fill tab.

Of course, you would edit the ranges to suit where you put your lookup table and your input fields.

Please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save