ExcelBanter - Combining the IF and List functions

If I understand what you want, you want the user to have a choice of 4
options in a drop-down list in B1 if A1 is No. If A1 is Yes, you want the
user to have no choice and B1 to be N/A.
You need VBA to do this. There is no way to combine an "IF" formula
with a drop-down (Data Validation) list.
This macro does what you want. Note that, as written, this macro requires
that your file have a sheet named "Utility". It is also required that A1:A4
of the Utility sheet contain the 4 items you want the user to choose from
when A1 of the active sheet is No and these 4 cells be named TheList. Also,
B1 of the Utility sheet must contain "N/A" without the quotes. Also, the
Data Validation in B1 must be "List" in the "Allow:" box and "=TheList"
(without the quotes) in the "Source:" box. This macro must be placed in the
sheet module of the sheet that contains the Yes/No in A1 and the drop-down
in B1. To access that module, right-click on the sheet tab, select View
Code, and paste this macro into that module.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Target.Address(0, 0) = "A1" Then
Application.EnableEvents = False
With Sheets("Utility")
If Target.Value = "Yes" Then
.[B1].Name = "TheList"
[B1].Value = "N/A"
Else
.[A1:A4].Name = "TheList"
[B1].ClearContents
End If
End With
Application.EnableEvents = True
End If
End Sub
wrote in message
oups.com...
Hi there, I'll try to explain my situation first

i have two cells next to each other

A1 | B1

Now A1 can either be "Yes" or "No"
If A1 is Yes, then B1 is automatically "N/A" - i can do this with a
simple IF statement.
BUT for B1 i want to use a drop down list of 4 options, so when A1 is
No then B1 can be any of the four options, BUT if A1 is No, then B1 is
automatically (and always) N/A

Please could someone help me do this...