Data Validation invalid data

[afdmello]

Hi Folks,
I am in a dilemma and wasted 2 hours to learn how this is possible. We have a drop down list in data validation which is taken from a defined name with abbreviations. This data validation has a " Stop" error alert. However it is still allowing the expanded name of the abbreviation to be entered. The data is invalid and is also circled by excel. The data validation drop down is W&WWT. I have attached a figure.
Hope someone knows the trick applied here.

Thanking you,

AFD

 

[KRice]

If the cell in question was previously set up with Data Validation using a drop down selector tied to a list, and the list did not include the verbose description, then I would assume that someone typed the long description in another cell and then pasted it into the cell. That action overwrites the data validation for that cell, and no red circle appears around this invalid entry. I can duplicate your result, but to do so, I pasted the long text in the drop-down cell, then clicked on the cell and chose Data Validation again to reinitialize the drop-down data validation for that cell. Since the cell already had invalid data in it, when I click on Data Validation > Circle Invalid Data, a red oval appears around my entry.

 

[afdmello]

Thank you

 

[KRice]

You're welcome...glad to help.

 

[Jdog12]

I recently learned the VBA code that can prevent anyone from copying and pasting to the cells which have validation list. All you do is right click on the sheet tab > view code then add in the VBA code.

Let's assume column H2 to H100 is for people to select items from your drop down list.

Below is the code:

Private Sub Worksheet_SelectionChange (ByVal Target As a Range)
If Not Application.Intersect(Target, Range("H2:H100")) Is Nothing Then
Application.CutCopyMode = False
End If
End Sub

 

[KRice]

@Jdog12, thanks for sharing this...this is a good tip.