Pick from list and Go to different cell (Excel 97)

Topic

New Reply

I have a user that created a pull down – Enter from list on his spreadsheets. Not sure how he did it as there are no macros or vb script attached. Example: Go to cell E3 and click on it. It will dislay a pull down at the lower right side with a Yes/No to select from.

We want to know how to do the followng: If No is selected – Enter No in the Cell and then GO TO CELL G3. If Yes is selected – Enter Yes in the Cell and then GO TO CELL H3.

I know its not much to work from, but I will try and get more details on how he created the updown/select from list.

Reply | Quote

Replies

The drop down list was created by click on cell E3, select “Data”->”Validation” and in the “Allow” drop down box select “List” and in the “Source” box below it type “Yes,No” (no quotation marks).
Then in cell E4 type =if(e3=”Yes”,”Go to cell h3″,”Go to cell G3)
The validation is a great way to limit entries by users. You can enter the values like I showed above or you can enter in the source a data range. For example, in cells F1 to F4 you could have “Yes”, “No”, “Sometimes” & “Never”. In the source box you would type =$F$1:$F$4 and when you open the drop down box you would see the four options.
Good luck I hope this helps.
David

Reply | Quote

The drop down list was most likely created using conditional formatting. If so, then the following VBA code in the Worksheet Change event routine should accomplish what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Worksheets("Sheet1").Range("A1")) Is Nothing Then
        If Worksheets("Sheet1").Range("E3").Value = "Yes" Then
            Worksheets("Sheet1").Range("H3").Select
        Else
            Worksheets("Sheet1").Range("G3").Select
        End If
    End If
End Sub

If the list box was done using the control toolbox or the Visual Basic toolbox or some other method, then something else might have to be used.

Reply | Quote

Use the Data validation method as described by Stats to restict cell values, and if you wish to automatically navigate to cells based on th eselection try using something like :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target.Cells(1), [E3]) Is Nothing Then
        Select Case UCase(Target.Value)
            Case Is = "NO"
                [G3].Select
            Case Is = "YES"
                [H3].Select
        End Select
    End If
End Sub

Right click on the tab of the sheet concerned and enter the above code.

Andrew C

Reply | Quote