up vote 0 down vote favorite
share [g+] share [fb]

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.

link|improve this question
25% accept rate
feedback

1 Answer

up vote 0 down vote

Just modify the formula using an IF statement:

=IF(OR(B2="No",ISERROR(<existing reference>)),"", <existing reference>)

where B2 is the Run? cell, and <existing reference> is how you currently pull the Tester value in.

link|improve this answer
You never really need data validation, but it does make things easier. For my case, I actually have other columns referencing the Tester column and looking up data in a different table based on its value. In the end, I'd like them to select a tester and have the other columns automatically refresh from the selected value instead of making them find the list of correctly formatted tester names and doing a copy/paste so the other columns calculate their values correctly. – bsh152s Aug 17 '11 at 19:40
@bsh152s, are you saying that you don't have an existing reference to pull the data from the Tester list now, but are pasting it in. If so you need to give those details, if you want more details in the formula. Either way, for Data Validation, just allow blanks. After all that, I not sure I'm getting what you want exactly right, so if not, please edit a lot more details into your question. – Lance Roberts Aug 17 '11 at 19:47
I've tried to clarify the question a bit more. – bsh152s Aug 17 '11 at 20:26
@bsh152s, I've modified it a bit, so if either the Run? value is "No" or the Tester no longer exists, then it puts a blank in the space. This will work even after you removed "John Doe" from the list. Data Validation wouldn't work that way, and I don't see it being useful on this column for this purpose. – Lance Roberts Aug 17 '11 at 20:45
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.