Data Validation with Keyword search

[RAMU]

Hi,
In My Sheet2 There are about 4000 names in column B
As an instance giving 5 only, like Bruce Lee, Jackey Chang, Silvester Stalone, Silva Arora, Jack Maa
Now in Sheet1 Column B" every cell I need to put Data Validation along with keyword search option, mean if I write Sil, two names will be shown "Silvester Stalone & Silva Arora", or if I write Jack then also two names will be shown "Jackey Chang & Jack Maa". Similarly if I write cha, then also Jackey Chang will be shown. Ans the names which will be shown I shall select any one of those & shall enter in that particular cell.
The same process will be applicable for all cells of Column B in Sheet1. But the main Data are in Column B of Sheet2.

Is there any way out to apply such searchable Data Validation in each cell ? Pls note it is quite impossible for me to use combo box in each cell manually.
Regards
RAMU

 

[Akuini]

Is there any way out to apply such searchable Data Validation in each cell ? Pls note it is quite impossible for me to use combo box in each cell manually.

Please, check this out:
Search deList

 

[RAMU]

Please, check this out:
Search deList

Hi,
Excellent, no doubt it is serving my purpose. only one option, if can be possible, instead of Alt + Right if it could be possible any single key, it can be more user friendly. Otherwise it is no doubt a great job, Hats off to you.

 

[HighAndWilder]

Hi,
Excellent, no doubt it is serving my purpose. only one option, if can be possible, instead of Alt + Right if it could be possible any single key, it can be more user friendly. Otherwise it is no doubt a great job, Hats off to you.

You can reassign a function key to Alt_Right

Public Sub subMain()
  
  Application.OnKey "{F1}", "subAltRight"
      
End Sub

Public Sub subAltRight()

  Application.SendKeys ("^{Right}")

End Sub

Sub subReset()
  
  Application.OnKey "{F1}"
  
End Sub

 

[Akuini]

if can be possible, instead of Alt + Right if it could be possible any single key,

Try this:

1. Open VBA window (press ALT+F11)
2. Open VBAProject Search_deList
3. Open module xDav_1 > check out this part:

Option Explicit
'shortcut key to show the userform:
Public Const xdvKey As String = "%{RIGHT}"   'Alt+Down Arrow
'Public Const xdvKey As String = "{F12}"

'In a range with data validation (with List type)
'pressing ALT+RIGHT will show the Useform.
'To change the shortcut: change "%{RIGHT}". Refer to this article:
'https://docs.microsoft.com/en-us/office/vba/api/excel.application.onkey
'============================================================================================

So, for example, if you want to change shortcut key to F12, then change this part:

Public Const xdvKey As String = "%{RIGHT}"   'Alt+Down Arrow
'Public Const xdvKey As String = "{F12}"

to this:

'Public Const xdvKey As String = "%{RIGHT}"   'Alt+Down Arrow
Public Const xdvKey As String = "{F12}"

Save the project then restart Excel.

Note: the description says Alt+Down Arrow, it's wrong, it should be Alt+Right Arrow, but it won't affect the code.

 

[HighAndWilder]

You can reassign a function key to Alt_Right

Public Sub subMain()
 
  Application.OnKey "{F1}", "subAltRight"
     
End Sub

Public Sub subAltRight()

  Application.SendKeys ("^{Right}")

End Sub

Sub subReset()
 
  Application.OnKey "{F1}"
 
End Sub

Correction. % instead of ^.

Option Explicit

Public Sub subMain()
  
  Application.OnKey "{F1}", "subAltRight"
      
End Sub

Public Sub subAltRight()

  Application.SendKeys ("%{Right}")

End Sub

Sub subReset()
  
  Application.OnKey "{F1}"
  
End Sub

 

[RAMU]

Thanks to both of you for a great solution, the major issue is if I try to apply this for my office purpose, I have to install the add-in to every user's system & all users don't use same version of office. it could be better if I could get a formula driven solution.
Apart from this I must give a BIG thanks to both of you from core of my heart.

 

[Akuini]

it could be better if I could get a formula driven solution.

Sorry, I don't know how to do this with formula.
Hopefully someone else can help.

 

[Akuini]

... or probably check out this one:
macro-to-create-searchable-data-validation-combobox

 

[Cubist]

Try this:

Book1
	A	B	C	D	E
1
2	Master List	Seach List		Search name:	Jack
3	Bruce Lee	Jackey Chang		Validation Cell ->	Jackey Chang
4	Jackey Chang	Jack Maa
5	Silvester Stalone
6	Silva Arora
7	Jack Maa
Sheet5

Cell Formulas
Range	Formula
B3:B4	B3	=FILTER(A3:A7,ISNUMBER(SEARCH(E2,A3:A7)),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E3	List	=$B$3#