Presenting a list conditionally

[jrod9369]

Is it possible to present different list on spreadsheet that is dependent on a value of cell on another sheet? It sounds simple but I can't seem to find out how. I have a 4 list that I want to show up conditionally on a spread sheet. I could try hiding rows but I thought Excel 2010 would have a more elegant way of doing this. Any help would be appreciated.

 

[jrod9369]

I was wondering if Pivot Tables would work for this? I thought it was a simple problem but I am learning it is possibly not possible in excel 2010.

 

[jrod9369]

Here is more detail. I have trimmed the need to a smaller piece to help troubleshoot.

I created a validation pull down. It has two options. If I select option 1 I want a list to appear in column c6 and if I choose the second option the I want another list to appear in c6. The list will have multiple entries. One will have 3 entries and the other will have 5 so it needs to fill c6-c9 and c6c11 respectably.

Help I have been beating head on this. Pointing me in the right direction will help....

 

[sastoka]

Hi

Ttry to copy this code in the spreahsheet your want the dropdown list to appear

Private Sub Worksheet_Activate()

    If Worksheets("[COLOR=#00ff00]Sheet1[/COLOR]").Range("[COLOR=#ffd700]A1[/COLOR]") = [COLOR=#40e0d0]0[/COLOR] Then
        Range("[COLOR=#0000ff]A2[/COLOR]").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=[COLOR=#dda0dd]Sheet2!$D$1:$D$5[/COLOR]"
            .IgnoreBlank = True
        End With
    Else
        Range("[COLOR=#0000ff]A2[/COLOR]").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=[COLOR=#ff0000]Sheet2!$E$1:$E$6[/COLOR]"
            .IgnoreBlank = True
        End With
    End If
    
End Sub

in this.

Sheet1 is the sheet where the cell you want to test is
A1 is the cell you want to test
A2 is the celle where you want to insert the list
Sheet2!$D$1:$D$5 is where the first list is located
Sheet2!$E$1:$E$6 is where the second list is located
0 is the condition

Let me know if it works.

 

[jrod9369]

I have tried this but I am getting a Run Time 1004 error. Application-defined or object error. But I get the idea and I will track it down. Thanks so much for the help.