How to use Drop-Down Lists and Conditional Formulas
Category: [Excel] Demo Available
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA to find and replace or If/Then with conditional formatting |
| How to add a second condition to conditional formatting |
| How to do conditional formatting based on another cell having data |
| How to use conditional format to highlight the blank cells |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.