Can you Add a drop down list within an if statement? 1

[eit09]

I have a data validation list (pull down) in cell A2. What I would like to do is use an if statement [=IF(A1="YES",Pull Down List,"")]. forcing cell A2 to have a pull down if A1 says YES and a blank cell if A1 says no. Is there a tool or formula to replace the verbiage Pull Down List in my formula above? The pull down list reference is B5:B30.

 

[onlyadrafter]

Hello,

can be done via macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" And Target.Value = "YES" Then
        With Range("A2").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$B$5:$B$30"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
    Else
        With Range("A2").Validation
            .Delete
        End With
    End If
End Sub

This needs to go into the relevant sheet code window, not a standard module.

It assumes that cell A1 is manually entered.


Hello,

also possible with formula but you will have the drop down arrow visible, with no options. In Cell A2 use DATA --> VALIDATION and select LIST and enter this formula

=IF($A$1="YES",$B$5:$B$30,"")

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

 

[eit09]

The formula works until a specific value is taken from the list. When I change A1 to No A2 retains the value chosen from the list instead vs going blank. Do you have any suggestions on modifying the formula to follow the if statement when A1 is changed? I did not try the macro as I don't speak that Jargon very fluently.

 

[yakpol]

You can make drop down list range dynamic depenidng on the value in A1 (Yes/No).
1. Create dynamic range name:
Formulas/Name Manager/New. Enter Name: List_ Refers to: =if(A1="Yes",DropDownList, EmptyCell") , Ok
2. Assign it to the input cell
Refer to this range in Data/Data Validation/Settings Allow: List Source: =List_ /Error Alert: clear check box Show error...

Not perfect though, you may want to use macro like provided by onlyadrafter to clear the value when A1 turns to "No".

Hope it helps!

Yakpol.

 

[eit09]

@onlyadrafter,

I tried the macro and it did not run for me. You noted that A1 is assumed manually, but I have a list. If I take your macro above, copy and paste the macro what would I need to change for it to work with a list in A1? I saved the macro and named it Clean_Up in the attached file if you wanted to take a look. I agree that a formula will give me what I am looking for.

 

[onlyadrafter]

Hello,

AS you have renamed it, you have not put the macro in the correct place.

When you go to the VB code window, you need to look in Microsoft Excel Objects under your spreadsheet name. Here you will find a list of the sheets/tabs in you spreadsheet. Find the one that has your drop downs, double click it. A window will appear and place the code in my original post in there.

It does work with a drop down in cell A1.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

 

[eit09]

@onlyadrafter,

With the steps listed below are you able to see where I veered wrong?
1.opened the visual basics editor
2.opened the object browser
3.double clicked on sheet 1
4.Pasted macro from original post
5.Clicked on View Microsoft Excell
6.Clicked on Macros and selected Clean_Up
7.Clicked run and got an compile error.

Thanks!

 

[onlyadrafter]

Hello,

don't seem to be able to access your file from the link.

So have created a file for you to view.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

 

[yakpol]

OnlyDrafter,
I modified your file to demonstrate no VBA approach

Regards,

Yakpol