autofill from vlookup

[HappyChappy]

i have to sheets sheet one coloum D is where the name goes. it uses vlookup to to populate coloum G with the two letter found on sheet2 coloum L
it works fine apart from spelling mistakes. is it possible to start typing the name in D and it to start prompting the name as you start to type so you can choose from a name shown.
it is always a letter then fullstop followed by the surname no spaces
the formula for the vloopup is as follows:- =IFERROR(IF(D3="","",VLOOKUP(D3,Lookups!$K$1:$L$80,2,FALSE)),"") but i don't think this matters as it's just the name in colum D that I want to auto lookup.
As you can see some of the names are quite challenging to remember the spellings.
I don't want to lock the cell as sometimes new names are needed to be added and later the sheet 2 would be updated to reflect the changes

 

[Yongle]

You need to create a searchable dropdown as illustrated in these 2 videos
I suggest you watch Video1 first even if FILTER function is not available to you

Video1
The new FILTER function (if available in your version of Excel) makes life easy!

Video2
Otherwise here an old method which is more cumbersome

 

[HappyChappy]

Great thanks for the help only one question how did the video make the drop down list bigger my list is so tiny it's hard to read.

 

[Yongle]

Away from PC now so cannot test anything until tomorrow
Did you use video1 or video2 solution?

In the meantime try putting this code in the sheet code window - it may even suffice long term!
right click on sheet tab \ View Code \ paste code there

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 4 Then ActiveWindow.Zoom = 200 Else ActiveWindow.Zoom = 100
End Sub

 

[HappyChappy]

Away from PC now so cannot test anything until tomorrow
Did you use video1 or video2 solution?

In the meantime try putting this code in the sheet code window - it may even suffice long term!
right click on sheet tab \ View Code \ paste code there

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 4 Then ActiveWindow.Zoom = 200 Else ActiveWindow.Zoom = 100
End Sub

 

[HappyChappy]

Used number 2 as sometimes sheets are used on 2007. Silly question just thought of the function is it saved with the workbook otherwise it will only work on my pc

 

[Yongle]

Everything is contained within the workbook. So anyone with a copy of the workbook can use it.
They should be told to enable macros for window zoom to work for them

 

[HappyChappy]

working fantasticly.. love the zoom can that be adapted to handle more than one cloumn? The only problem is that The dropdown list in D2 works great but when i goto d3 it is effected by whats already in d2 and i need to have different values from d2:d30 i'm sure it's just the way the cells are locked but baffled me.

 

[Yongle]

Replace the one line with something like this:

Here selecting any cell in columns 4 or 7 or 12 would result in the bigger zoom

Select Case Target.Column
   Case 4, 7, 12
      ActiveWindow.Zoom = 200
   Case Else ActiveWindow.Zoom
      ActiveWindow.Zoom = 100
End Select

 

[Yongle]

The dropdown list in D2 works great but when i goto d3 it is effected by whats already in d2 and i need to have different values from d2:d30

I am trying to understand your requirement

Are you saying that the values in D2:D30 must be unique ?
(ie a name must only be selected once)