Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <pre>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
    </pre>


    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.
    Legare Coleman

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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 : <pre>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</pre>

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

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •