Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation in a Conditional Statement
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(A11, 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation in a Conditional Statement
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 "JanW" wrote in message ... 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(A11, 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation in a Conditional Statement
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(A11, "Apples","") =IF(A11, "Bananas","") =IF(A11, "Cherries","") =IF(A11, "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 On Jun 6, 1:48 pm, JanW wrote: 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(A11, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I include a "validation drop down" in a conditional statement? | Excel Worksheet Functions | |||
If statement with validation list | Excel Worksheet Functions | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
How do I use a conditional (IF) statement in Data Validation? | Excel Worksheet Functions | |||
conditional statement | New Users to Excel |