CODEWITHSUNDEEP
exceldrop-down-menu
Jey Legarie
Jey Legarie

Reputation: 43

Excel, Want to Create Dropdown Based on a "yes" Cell

The issue is pretty simple, but I can't seem to solve it or find an answer.

I have one cell A2 that activates with a yes/no based on what the user enters elsewhere.

I would like to create a drop-down list in another cell A3 only if A2 has a "yes" inside. From what I can tell, this can only be done with Data Validation, but it keeps throwing errors.

Upvotes: 0

Views: 137

Answers (2)

David G
David G

Reputation: 2347

EDIT: Oh well It appears VBA was completely unnecessary for this use. I'm not deleting the answer because it would work, but I don't reccomend this over the easier method!

I would use VBA for that. If you aren't afraid to get dirty with some code, here's something I believe will work. If you are an absolute zero in VBA I can give more detailed help.

Open up the Visual Basic Editor and copy/paste this:

'this means it activates when there is change in your worksheet:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)         
    If Not Intersect(Target, Range("A2")) Is Nothing Then 'Checks that the change occurs in the cell A2       

        If Range("A2") = "Yes" Then 'only enters this if it changed to yes
            With Range("A3").Validation 'this little block creates the list. More on this after the code
                .delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=Paramètres!$C$1:$C$5"
            End With 
        End If

        If Range("A2") = "No" Then 'only does this if it changed to no
            Range("A3").Validation.delete 'this deletes the list.                    
        End If

    End if
End sub

So the list, in our case, is created or deleted when a change occurs in cell A2. The list contains the items you will have to write in cells C1 to C5. (example: In c1 write "hello", in c2 write:"goodbye" etc). If you want more or less inputs you can change C1 to C3 or C10, as you like. You can also make put the list elsewhere (column E for example) by changing the C. After writing your list in a column, I suggest hiding that column because it is unnecessary to see it.

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

With Data Validation for a drop down list, when you are creating it, you can use "If" statements. Choose "List" from "allow", then for the Source, something like =if(A2="Yes",B2:B6,C2). Note, I assumed that your list of data is B2:B6, and in C2 is some string you want to return if A2 is not "Yes".

Note: If A2 is not "Yes", and you want the list in A3 to then say "No list necessary", you can't just use that string in the IF statement for the validation - you need to put that string in, i.e. C2, and use the formula above. Can't put strings in the data validation IF statement for some reason.

Upvotes: 3

Related Questions