Type in field to jump to first instance of what was typed from a drop down list

[ipbr21054]

Hi,
On my worksheet called INV i have a drop down in cell G13
Cell G13 is linked to another worksshet called DATABASE & looks at the customers names in column A

When i create an invoice i use the drop down & then scroll to the customer in question & make the selection.
Once done all my other cells on the INV worksheet get populated with the data on the customers row on the DATABASE sheet.

The above works fine.

Now my list of customers on the worksheet DATABASE is getting longer i am spending longer scrolling through the list of names on INV cell G13
So i need to look at this.

Any suggestions ?
Only one i can think of is if i type T in G13 when i then click the drop down arrown i see the customer selection list start at T as opposed at the begining with A

The info for G13 drop down i have found is shown in screenshot

 

[ipbr21054]

I think maybe the best way to attack this is by the following.

The drop down list in G13 shows ALL my customers from the worksheet DATABASE.
There would be no point showing a customer in the drop down list if they have paid.
Once paid they get assigned an invoice number against there name on worksheet DATABASE in column P.

So i think best the drop down list should only show the customer where the cell in column P is empty.
The range for my customers is Row 6 & down the page.
The drop down list will then be much smaller than before & will be even smaller once customer has paid & i have assigned an invoice number to there name

 

[mumps]

Could you upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[ipbr21054]

Link supplied.
SHARING

Two worksheets DATABASE & INV

Go to INV & cell G13
You will see the list contains all the customers from my sheet DATABASE but there is no need to show these customers in this list as they have an invoice number asigned to them.
See DATABASE column P for invoice numbers.
A001 B001 C001 D001 E001 F001


So the only customers that should be shown in the drop down list will be,
CHARLEY 001 BOB 001 ANDY 001 because if you look in column P they have NO invoice number asigned to them

My G13 list will then only show customer that have invoice number asigned to them.
Thanks

 

[mumps]

Place this macro in the "INV" sheet code module. When you click on G13, the drop down list will be updated. If you add invoice number in column P, the drop down will update automatically.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("G13")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rName As Range, Val As String
    For Each rName In Sheets("DATABASE").Range("A6", Sheets("DATABASE").Range("A" & Rows.Count).End(xlUp))
        If rName.Offset(, 15) = "" Then
            If Val = "" Then Val = rName Else Val = Val & "," & rName
        End If
    Next rName
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Val
    End With
    Application.ScreenUpdating = True
End Sub

 

[ipbr21054]

Thanks for your time it workes fine

 

[mumps]

You are very welcome.

 

[ipbr21054]

Hi,@mumps
Today i went to print an invoice but got an error.
Below is the code & the line in Red is what it debugs to.

Do you see why thanks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("G13")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
   
    Dim rName As Range, Val As String
    For Each rName In Sheets("DATABASE").Range("A6", Sheets("DATABASE").Range("A" & Rows.Count).End(xlUp))
        If rName.Offset(, 15) = "" Then
            If Val = "" Then Val = rName Else Val = Val & "," & rName
        End If
    Next rName

    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Val
    End With
    Application.ScreenUpdating = True
End Sub

I should advise that when i run this an invoice number is now entered in column P for customer in question

 

[ipbr21054]

OK
So just moving on i deleted that line of code shown in Red & i was able to print the invoice.

BUT

Can you advise what it actualy doing & is it ok to leave it deleted or do we leave it there & add some extra code.

Thanks

 

[mumps]

So the only customers that should be shown in the drop down list will be,
CHARLEY 001 BOB 001 ANDY 001 because if you look in column P they have NO invoice number asigned to them

That line of code is necessary to create the drop down list of only those customers that do not have an invoice number in column P. If an error is generated, that would indicate that all customers have an invoice number in column P so the drop down list would be empty. What do you want to do if all customers have an invoice number in column P