Multiple Searchable Data Validation For Older version of Excel

[Jose Mathew]

multiple data validation .xlsm
	A	B
1	Multiple Searchable Data Validation
2	Multiple data Validation	1000 English words
3	1	your
4	2	there
5	3	southern
6	4	according
7	5
8	6
9	7
10	8
11	9
12	10
13	11
14	12
15	13
16	14
17	15
18	16
19	17
20	18
21	19
22	20
23	21
24	22
25	23
26	24
27	25
28	26
29	27
30	28
31	29
32	30
33	31
34	32
35	33
36	34
37	35
38	36
39	37
Home

Cells with Data Validation
Cell	Allow	Criteria
B3:B39	List	=OFFSET(Data!$K$2:$K$100,0,0,COUNTA(Data!$K$2:$K$100)-COUNT(Data!$K$2:$K$100))

multiple data validation .xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Words					Formula1	Formula2	Formula3		Heads		Searchable cell	thern
2	a					1		810		southern
3	ability					2				0
4	able					3				0
5	about					4				0
6	above					5				0
7	accept					6				0
8	according					7				0
9	account					8				0
10	across					9				0
11	act					10				0
12	action					11				0
13	activity					12				0
14	actually					13				0
15	add					14				0
16	address					15				0
17	administration					16				0
18	admit					17				0
19	adult					18				0
20	affect					19				0
21	after					20				0
22	again					21				0
23	against					22				0
24	age					23				0
25	agency					24				0
26	agent					25				0
27	ago					26				0
28	agree					27				0
29	agreement					28				0
30	ahead					29				0
31	air					30				0
32	all					31				0
33	allow					32				0
34	almost					33				0
35	alone					34				0
36	along					35				0
37	already					36				0
38	also					37				0
39	although					38				0
40	always					39				0
41	American					40				0
42	among					41				0
43	amount					42				0
44	analysis					43				0
45	and					44				0
46	animal					45				0
47	another					46				0
48	answer					47				0
49	any					48				0
Data

Cell Formulas
Range	Formula
G2:G49	G2	=ROWS(B$2:B2)
H2:H49	H2	=IF(ISNUMBER(SEARCH(N$1,B2)),G2,"")
I2:I49	I2	=IFERROR(SMALL($H$2:$H$1001,ROWS(H$2:H2)),"")
K2:K49	K2	=IFERROR(INDEX($B$2:$E$1001,$I2,COLUMNS($I$2:I2)),0)

Cells with Data Validation
Cell	Allow	Criteria
N5	List	=OFFSET(#REF!,0,0,COUNTA(#REF!)-COUNT(#REF!)-1)

 

[Jose Mathew]

along with a simple VBA
Sub sendkey()

Worksheets("Data").Range("N1") = ActiveCell.Value
ActiveCell.Select

SendKeys "%{DOWN}"


End Sub