How to use Drop-Down Lists and Conditional Formulas

Requirement:

The user is currently working with a fairly large data set (a sample of which is attached) that contains a drop-down list of options that respondents can choose from.

The user is wondering whether there is a way to write a formula and preserve that formula when certain options are selected, but remove that formula from that column when others are present.

In other words, the user would like to remove the formula when "Other" is selected (or if there is just an empty cell); when someone selects "Other", there should be nothing at all (not even the formula) in the cell, but NA should appear when the other options are selected.

https://www.mediafire.com/file/cpixi...Book.xlsx/file

Solution:

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in A2.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Asian", "AfricanAm", "Hispanic"
            Target.Offset(0, 1) = "N/A"
        Case Else
            Target.Offset(0, 1) = ""
    End Select
End Sub

If the user wanted to do the same thing with data in columns P and Q this time, how would I edit the macro?

So:

Learning = N/A
ASDS = N/A
ADDH = N/A
LMAP = N/A
Other = Blank

Learning, ASDS, ADDH, LMAP and Other are all in column P, the N/As and Blanks are in column Q.

(Attached an updated workbook for reference)

https://www.mediafire.com/file/i5r8e...ook2.xlsx/file

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A,P:P")) Is Nothing Then Exit Sub
    If Target.Column = 1 Then
        Select Case Target.Value
            Case "Asian", "AfricanAm", "Hispanic"
                Target.Offset(0, 1) = "N/A"
            Case Else
                Target.Offset(0, 1) = ""
        End Select
    ElseIf Target.Column = 16 Then
        Select Case Target.Value
            Case "Learning", "ASDS", "ADDH", "LMAP"
                Target.Offset(0, 1) = "N/A"
            Case Else
                Target.Offset(0, 1) = ""
        End Select
    End If
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Mumps.