INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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.

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

Hello,

can be done via macro.

CODE

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

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

(OP)
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.

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

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.

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

(OP)
@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.

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

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

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

(OP)
@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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close