Solved Excel 2013 drop down lists

Microsoft Microsoft excel 2007 full vers...
December 3, 2015 at 15:02:11
Specs: Windows 7
Hi All, trying to create multi row/column drop down list of drivers that go over different routes. some go over more than others and some none at all.
i would like to search for a route which brings up the drivers names etc.

hope you can help. many thanks.


See More: Excel 2013 drop down lists

Report •

✔ Best Answer
December 10, 2015 at 03:02:24
A slight improvement, im now using the 'Like' operator so that you can for example, search for 'Ben' in the name field and it will return all names and routes for people whos name is like 'Ben' such as 'Ben' 'Ben smith', 'Benjamin something' etc

this is also true for the route names, you can enter a short route name such as 'Acton East' and it will return all the drivers names that have routes with 'Acton East' in them.

Dim SearchName As String
Dim SearchRoute As String
Dim uRange
Dim lRange
Dim Bcell As Range
Dim iResults As String

Private Sub CommandButton1_Click()
    
    ' Clears the output field
    Range("txtdetails").Value = Empty
    iResults = Empty
    
    'Sets the variable names to the names entered into the search fields
    If Range("A2") <> Empty Then
        SearchName = Range("A2").Value
    Else
        SearchName = Empty
    End If
    
    If Range("B2") <> Empty Then
        SearchRoute = Range("B2").Value
    Else
        SearchRoute = Empty
    End If
    
    'Sets the search range where the names and routes exist
    Set uRange = Sheet1.Range("A1")
    Set lRange = Sheet1.Range("A" & Rows.Count).End(xlUp)
    
    
    'Uses both the name and the route to perform and return search results
    If Range("A2") <> Empty And Range("B2") <> Empty Then

        For Each Bcell In Sheet1.Range(uRange, lRange)

            If Bcell.Value Like "*" & SearchName & "*" And Bcell.Offset(0, 1).Value Like "*" & SearchRoute & "*" Then
                iResults = iResults & Bcell.Value & " : " & Bcell.Offset(0, 1).Value & vbCrLf
                Range("txtdetails") = iResults
            End If

        Next Bcell

     End If
        
        
    ' Uses just the name to search and return results
    If Not IsEmpty(Sheet2.Range("A2")) And IsEmpty(Sheet2.Range("B2")) Then
         
        For Each Bcell In Sheet1.Range(uRange, lRange)
            
            If Bcell.Value Like "*" & SearchName & "*" Then
                iResults = iResults & Bcell.Value & " : " & Bcell.Offset(0, 1).Value & vbCrLf
                Range("txtdetails") = iResults
            End If
        Next Bcell
        
    End If
    
    
    ' Uses just the route to search and return results
    If IsEmpty(Sheet2.Range("A2")) And Not IsEmpty(Sheet2.Range("B2")) Then
             
        For Each Bcell In Sheet1.Range(uRange, lRange)
            
            If Bcell.Offset(0, 1).Value Like "*" & SearchRoute & "*" Then
                iResults = iResults & Bcell.Value & " : " & Bcell.Offset(0, 1).Value & vbCrLf
                Range("txtdetails") = iResults
            End If
            
        Next Bcell
        
    End If
    
End Sub




#1
December 3, 2015 at 16:51:18
Will have to have more specific info to offer any kind of assistance.

Post a small sample of our sheet, after you have read this HOW-TO which explains the
use of < PRE > tags to align your data.

http://www.computing.net/howtos/sho...

Please include Column Letter & Row Numbers.

MIKE

http://www.skeptic.com/


Report •

#2
December 5, 2015 at 09:45:12
Hi Mike.

i have created a working list by typing the name in one column and the route in the other.

names in column A routes in Column B

Anderson Nick Acton East Jn to Acton West via Mains, Reliefs, Goods, Receptions
Anderson Nick Acton West to West Ealing
Anderson Nick West Ealing to Southall West Jct

Ball David Acton East Jn to Acton West via Mains, Reliefs, Goods, Receptions
Ball David Acton West to West Ealing
Ball David West Ealing to Southall West Jct
Ball David West Ealing Plassers Yard
Ball David Southall West Jn to West Drayton

Chambers Owen (PQ) Oxley to Bushbury Jct
Chambers Owen (PQ) Oxley to Abbey Forgate Jct
Chambers Owen (PQ) Abbey Foregate Jct to Shrewsbury

when i select filter i can search for a route and all the names of drivers who go over that route (from within the filtered drop down box)

what i would like to do now is create a search box (or similar) where i can type in a search and results would show (in another box) just to make it look a bit smarter and blank out the names & routes (basicaly a blank page with search & display boxes.

hope this makes sense :-)

many thanks

Adam


Report •

#3
December 5, 2015 at 10:45:42
hope this makes sense

Yes, it does, but I belive you will need VBA to get what you want
and unfortunately my VBA skills are just above nil.

Hopefully, someone with VBA skills will offer a solution.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
December 5, 2015 at 11:05:06
Here is a site which might be of interest:

http://www.thespreadsheetguru.com/b...

You can download the example data file at the bottom.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#5
December 10, 2015 at 02:20:09
Hi,

Without knowing the exact layout of your worksheets and data, i have had a go at this, there is scope for improvement but as iv said, im not sure how your worksheets look.

What you will need is

A worksheet called 'Sheet1'
the driver name in column A - starting from A1
the route names in column B - Starting from B1

A worksheet called 'Sheet2'
A title cell called 'Name' in A1
A title cell called Route in B1

A2 will be your search cell for Name
B2 will be your search cell for Route

a command button anywhere on the sheet - iv placed it in C2

in sheet2 select range A5 to B20 - now to the left of the formula bar you will see a box with the word A5 in it, select that and change A5 to txtdetails

open VBE by holding Alt and pressing F11
double click sheet2 in the VBE and paste the following code

if all else fails, drop me a PM with your email address and i will send you the workbook i have created.

Dim SearchName As String
Dim SearchRoute As String
Dim uRange
Dim lRange
Dim Bcell As Range
Dim iResults As String

Private Sub CommandButton1_Click()
    
    ' Clears the output field
    Range("txtdetails").Value = Empty
    iResults = Empty
    
    'Sets the variable names to the names entered into the search fields
    If Range("A2") <> Empty Then
        SearchName = Range("A2").Value
    Else
        SearchName = Empty
    End If
    
    If Range("B2") <> Empty Then
        SearchRoute = Range("B2").Value
    Else
        SearchRoute = Empty
    End If
    
    'Sets the search range where the names and routes exist
    Set uRange = Sheet1.Range("A1")
    Set lRange = Sheet1.Range("A" & Rows.Count).End(xlUp)
    
    
    'Uses both the name and the route to perform and return search results
    If Range("A2") <> Empty And Range("B2") <> Empty Then

        For Each Bcell In Sheet1.Range(uRange, lRange)

            If Bcell.Value = SearchName And Bcell.Offset(0, 1).Value = SearchRoute Then
                iResults = iResults & Bcell.Value & " : " & Bcell.Offset(0, 1).Value & vbCrLf
                Range("txtdetails") = iResults
            End If

        Next Bcell

     End If
        
        
    ' Uses just the name to search and return results
    If Not IsEmpty(Sheet2.Range("A2")) And IsEmpty(Sheet2.Range("B2")) Then
         
        For Each Bcell In Sheet1.Range(uRange, lRange)
            
            If Bcell.Value = SearchName Then
                iResults = iResults & Bcell.Offset(0, 1).Value & vbCrLf
                Range("txtdetails") = iResults
            End If
        Next Bcell
        
    End If
    
    
    ' Uses just the route to search and return results
    If IsEmpty(Sheet2.Range("A2")) And Not IsEmpty(Sheet2.Range("B2")) Then
             
        For Each Bcell In Sheet1.Range(uRange, lRange)
            
            If Bcell.Offset(0, 1).Value = SearchRoute Then
                iResults = iResults & Bcell.Value & vbCrLf
                Range("txtdetails") = iResults
            End If
            
        Next Bcell
        
    End If
    
End Sub


message edited by AlwaysWillingToLearn


Report •

#6
December 10, 2015 at 03:02:24
✔ Best Answer
A slight improvement, im now using the 'Like' operator so that you can for example, search for 'Ben' in the name field and it will return all names and routes for people whos name is like 'Ben' such as 'Ben' 'Ben smith', 'Benjamin something' etc

this is also true for the route names, you can enter a short route name such as 'Acton East' and it will return all the drivers names that have routes with 'Acton East' in them.

Dim SearchName As String
Dim SearchRoute As String
Dim uRange
Dim lRange
Dim Bcell As Range
Dim iResults As String

Private Sub CommandButton1_Click()
    
    ' Clears the output field
    Range("txtdetails").Value = Empty
    iResults = Empty
    
    'Sets the variable names to the names entered into the search fields
    If Range("A2") <> Empty Then
        SearchName = Range("A2").Value
    Else
        SearchName = Empty
    End If
    
    If Range("B2") <> Empty Then
        SearchRoute = Range("B2").Value
    Else
        SearchRoute = Empty
    End If
    
    'Sets the search range where the names and routes exist
    Set uRange = Sheet1.Range("A1")
    Set lRange = Sheet1.Range("A" & Rows.Count).End(xlUp)
    
    
    'Uses both the name and the route to perform and return search results
    If Range("A2") <> Empty And Range("B2") <> Empty Then

        For Each Bcell In Sheet1.Range(uRange, lRange)

            If Bcell.Value Like "*" & SearchName & "*" And Bcell.Offset(0, 1).Value Like "*" & SearchRoute & "*" Then
                iResults = iResults & Bcell.Value & " : " & Bcell.Offset(0, 1).Value & vbCrLf
                Range("txtdetails") = iResults
            End If

        Next Bcell

     End If
        
        
    ' Uses just the name to search and return results
    If Not IsEmpty(Sheet2.Range("A2")) And IsEmpty(Sheet2.Range("B2")) Then
         
        For Each Bcell In Sheet1.Range(uRange, lRange)
            
            If Bcell.Value Like "*" & SearchName & "*" Then
                iResults = iResults & Bcell.Value & " : " & Bcell.Offset(0, 1).Value & vbCrLf
                Range("txtdetails") = iResults
            End If
        Next Bcell
        
    End If
    
    
    ' Uses just the route to search and return results
    If IsEmpty(Sheet2.Range("A2")) And Not IsEmpty(Sheet2.Range("B2")) Then
             
        For Each Bcell In Sheet1.Range(uRange, lRange)
            
            If Bcell.Offset(0, 1).Value Like "*" & SearchRoute & "*" Then
                iResults = iResults & Bcell.Value & " : " & Bcell.Offset(0, 1).Value & vbCrLf
                Range("txtdetails") = iResults
            End If
            
        Next Bcell
        
    End If
    
End Sub



Report •

#7
January 5, 2016 at 02:39:53
Hey PipKin,

just wondering if you managed to get this working and if so, does it work for you? i know you set the best answer, but i just wanted to know how it was working for you?


Report •

Ask Question