Question of Data Validation

Copper Contributor


I am using Data Validation conditional to another cell content, using "Indirect" function: a drop-list will appear on cell B3 conditioned to the text selected on A3.

Once a selection is made, if I change the selection on A3, I sill have the previus how can I make that B3 content cleared up or get an error message?



3 Replies


You'd have to use VBA code for that. It would work in the desktop version of Excel for Windows and Mac, not in Excel Online or on Android and iOS. Would that be OK? If so, do the following:

  • Right-click the sheet tab.
  • Select 'View Code' from the context menu.
  • Copy the code listed below into the worksheet module.
  • Switch back to Excel.
  • Save the workbook as a macro-enabled workbook (*.xlsm).
  • Make sure that you allow macros when you open the workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A3"), Target) Is Nothing Then
        Application.EnableEvents = False
        Range("A3").Offset(0, 1).ClearContents
        Application.EnableEvents = True
    End If
End Sub
Thanks. I have another question I have a list with duplicated values on it. I do data validation with this list to get a drop list. When done with one computer I get a drop list with only unique values out of that list (that is what I want), but if I do the same - same file - in another computer, I get a drop-list with duplicated values (many lines with same text). Do you know what the reason would be? Thanks Alvaro


Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?