Overriding the value in cell based on an entry in drop down list in another cell

[Godot60]

I have cell in a worksheet (E17) that populates based on an entry in another cell (e13) using the code: =IFERROR(VLOOKUP(E13,'Job code'!A2:G10000,7,FALSE),0).

However I have a another cell, W13 that contains a drop down list. If the person entering data selects any of several items in the drop down list I want it to override what is in cell E17 with a different piece of text "W00"

Any suggestions about how this might be accomplished would be much appreciated! I have tried a number of things that haven't worked. Thanks!

 

[BobUmlas]

Right-click the sheet tab, select View Code, enter this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$W$13" Then Range("E17").Value = "W00"
End Sub

But note you'll lose your formula in E17.

 

[Godot60]

Thanks! It works.

My one remaining challenge is that I only want E17 to populate with "W00" if certain items from the drop down list are selected. Any suggestions are welcome.

 

[Godot60]

Also, once you password protect the sheet I get an error message that says:"Runtime error '1004': Application-defined or object-defined error." Thanks.

 

[Godot60]

OK I figured it out and I'm posting the solution in case others were challenged by this issue. The answer was writing an if then formula that put on one response if particular items were chosen from a drop down menu in one cell, and otherwise entered a different response from a lookup table. See below:

=IF(OR(W13="Student",W13="Per Diem",W13="Temporary Employee",W13="Graduate Employee"),"W00 - Non Benefited",(IFERROR(VLOOKUP(E13,'Job code'!A2:G10000,7,FALSE), 0)))