I have a test planning worksheet with the following columns: Test Name, Run?, Tester, Tester Location, Environment, etc. The "Run?" column has data validation on for valid values of Yes and No. The "Tester" column also has data validation on and is referencing a list of testers somewhere else in the spreadsheet (through a named range). The "Tester Location" column is using an index/match formula to lookup it's value based on what is in the "Tester" column. Basically, I have a "TesterData" named range that includes various tester information, and I want to repeat this information in other worksheets, just so the user doesn't have to got back and forth between worksheets.
What I would like is that when someone marks a test not to be run (Run? = No), I want the Tester to be cleared out (or set to N/A). Basically, if we're not going to run a test, then it shouldn't be assigned a person. This would also cause the "Tester Location" column to be set to #N/A since a blank isn't in the TesterData named range. Is this possible?
I've got a somewhat related question about list data validation. For the case of the "Tester" column that's using list data validation, what if someone selects "John Doe" for a given row. But then lets say John Doe leaves the company and we remove him from the list. I'd like all cells referencing "John Doe" to be cleared but it feels like that is not how list data validation works. Is the clearing in this case possible? All in all, I'm still learning how data validation works.