Validation in a Conditional Statement

[Guest]

I posted this question yesterday, but it doesn't appear to be showing up
anywhere.

I'm using Excel 2003.

I'm trying to determine whether there is a way to include a validation drop
dowm list as a result of a conditional statement. In other words, If a
conditional statement is true, I'd like to display a drop down box, otherwise
zero.

As an example, I'd like to insert a formula in cell "A2" that says
"=If(A1>1, XYZ,0), and I'd like to be able to replace XYZ with a validation
drop down (it would be a piece of cake if "validation" was a function).

Anyone have any ideas? I received a notification yesterday that someone had
responded, but I couldn't find the response (or the question).

Thanks

 

[Bernie Deitrick]

Jan,

Copy the code below, right click the sheet tab, and paste the code into the window that appears.
Change the list

Formula1:="Value 1,Value 2,Value 3"

to whatever your desired DV list is.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub

Application.EnableEvents = False
If Target.Value > 1 Then
Range("A2").ClearContents
With Range("A2").Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Value 1,Value 2,Value 3"
.IgnoreBlank = True
.InCellDropdown = True
End With
Else
Range("A2").Validation.Delete
Range("A2").Value = 0
End If
Application.EnableEvents = True

End Sub

 

[Pete_UK]

I assume you have a list of values in your workbook which is used by
the data validation cell A2 - let's say you have "Apples", "Bananas",
"Cherries", "Pears", etc in a block of cells somewhere.

What you could do is to change this list to a series of formulae, like
so:

=IF(A1>1, "Apples","")
=IF(A1>1, "Bananas","")
=IF(A1>1, "Cherries","")
=IF(A1>1, "Pears","")

Cell A2 contains the data validation pointing to this list, but if A1
is less than 1 then the list doesn't contain anything, so although the
pull-down is still visible in A2, there is nothing to select. However,
if the value in A1 exceeds 1 then your list is re-instated and so you
can use A2 normally.

Hope this helps.

Pete