Changing one validation cell should reset another

Hi there! First time posting but this forum comes highly recommended. I'm fairly new to Excel but I'll try to make clear what I'm trying to do.

My sheet has a Data Validation drop down menu "Department", where the user selects a Department. Based on this selection, another Data Validation cell "Team Leader" gives a list of Team Leaders to choose from. This works perfect. Change the Department, you get a different list of names. The problem is that if I have both a Department and a Team Leader selected, and then I change the Department selection, it keeps the Team Leader cell filled with now invalid data (the Team Leader from the previous department). If I then click on the Team Leader cell, it has the correct names listed to choose from, but until I do that cell retains the old information. Is there any way to blank the "Team Leader" cell, or push data to it (I've included a Choose a Team Leader entry in my list of people), when I change my Department selection?

I've included a sample file to hopefully show what I'm trying to explain.

One thing you can do is to apply conditional formatting to cell Q3 using this formula:

=ISNA(MATCH(Q3,TEAM,0))

and then choosing say a white foreground on a red background (fill), so that the incorrect/invalid entry is highlighted immediately that you change the choice of department in L3.

Hope this helps.

Pete

Please Login or Register  to view this content.

Looking at JosephP's solution, I would expect you could implement a generalised solution for all cells in a worksheet without affecting workbook performance too much.

Please Login or Register  to view this content.