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

 

[HappyChappy]

from my sheet1 in diagram d3:d30 will all have different names from the list as they are all drivers and can only drive one vehicle at a time. So I need to have the dropdown list in each of d3:d30 so I can choose each driver fr each row

 

[HappyChappy]

the new vba code isnt working i copy it over and paste in into sheet and the 4th line case else active window.zoom is RED

 

[Yongle]

the new vba code isnt working i copy it over and paste in into sheet and the 4th line case else active window.zoom is RED

oops!
delete as indicated

Case Else ActiveWindow.Zoom

 

[HappyChappy]

yes each cell represents a driver of a vehicle so one person per cell

 

[Yongle]

from my sheet1 in diagram d3:d30 will all have different names from the list as they are all drivers and can only drive one vehicle at a time. So I need to have the dropdown list in each of d3:d30 so I can choose each driver fr each row

The obvious way would be to eiminate duplicates from the dropdown by tailoring the dropdown workings sheet.
- is your list of available drivers in column A of that sheet ?

 

[HappyChappy]

the new code makes the dropdown window extra wide but not zoomed in like the first vba code

 

[HappyChappy]

my list of names is in colunm K as in diagram 2

 

[HappyChappy]

The video you sent worked great for one cell looking up a list. but as you can see from my sheet1 i have 12 different cells that have different data in each so the validation needs to be in each cell not just the one. Is there a way to modify the routine you sent me as it does what I want but not in each cell as i want it to.

 

[Yongle]

Like I said in post#5 eiminate duplicates from the dropdown by tailoring the dropdown workings sheet.
2 columns need to be inserted in your dropdown workings sheet so that the list of available names is reduced every time a name is inserted in column D

Below I have used a similar technique to one used in the video that you followed
- Sheet1 is where drivers are allocated in column D
- Sheet2 helper columnB provides the match that can be used in column C
- formula in cell B2 (copied down)
=SUM(B1,(COUNTIF(Sheet1!D:D,A2)=0)*1)

As you can see only 8 names are available ....

Book1
	A	B	C
1	Names	Helper	Filtered List
2	Name01	0	Name02
3	Name02	1	Name03
4	Name03	2	Name04
5	Name04	3	Name05
6	Name05	4	Name06
7	Name06	5	Name07
8	Name07	6	Name08
9	Name08	7	Name09
10	Name09	8
11	Name10	8
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=SUM(B1,(COUNTIF(Sheet1!D:D,A2)=0)*1)
C2:C11	C2	=IFERROR(INDEX(A:A,MATCH(ROWS($B$2:B2),B:B,0)),"")

.... because 2 names have already been allocated in Sheet1 column D

Book1
	C	D	E
2		Drivers Name
3		Name01
4		Name10
5
6
7
8
Sheet1