Validation Lists...

[brans1982]

Hi,

I have created a validation list. Well, all I need is a drop down list but using the validation option is the only way i can seem to do it. Basically, what I want to do is select something from the list and when I do that for my page to scroll down to where that list referfers to e.g

Amsterdam
Paris
Munich

If the information on Munich is in the range A16:D20, when I click on Munich in the list (A1) I want the page to move to where the range is.

Is that possible? I am using Excel 2002 by the way. (work computer)

Thanks Folks! Look forward to seeing some help!

 

[SteveO59L]

Why not simply define the areas named ranges, and use the Name Box facility ?

 

[doofusboy]

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
        Select Case Range("A1").Value
            Case "Amsterdam"
                Range("A5").Select
            Case "Paris"
                Range("A10").Select
            Case "Munich"
                Range("A15").Select
        End Select
    End If
End Sub

 

[brans1982]

Hey Steve,

I'm kinda new to this, any chance you could give me an example?

Thanks.

 

[BGY23]

I don't know how you'd do that but what you could do is use Data>Advanced>Filters. Use the location of your drop down list as the criteria. then when you select i.e Amsterdam it will filter your data range to show just lines with Amsterdam on.

 

[SteveO59L]

The Name Box is in the near top left of my screen, just above the "A" for col A.

Select your range, then Insert, Name, Define, give your range a name. When you select the name box, the name of the defined range will be selectable, and when selected the cursor will automatically select the 1st cell of that range.