Multiple searchable drop down lists of the same array

[Filipzgela]

Hello,

i have a list of projects that almost all start with letters PR in sheet2 of my excel table. In sheet1 we have a form where the employees every day enter their working hours and projects that they worked at at those hours.
I want to create a dropdown list so that employees can only enter values found in sheet2, and that is pretty simple. The issue is that there are currently 500 different projects, so i would like to find a way for them to search the list while writing in sheet1. The rest of the sheet1 isn't really important.
On my computer the list is searchable if i do a simple data validation list, but it doesn't work on any other PC in the company that i've tried. So, i need a different way. Please help.
Sheet1 has 15 cells that would all have to have the searchable list, and all of those 15 cells can have different values.
We send that form to all the employees, and they then enter each their own informations.
Thank you very much.
Filip

 

[Rijnsent]

What you could do:
- make the data validation list somewhere on another sheet
- in your main form, add a field that's called "filter"
- so say the user types "reno" (for either the project named "Renovation" or the "Let's go to Reno Project", the data validation list could filter with that criteria, thereby limiting the number of options in the dropdown.
The newer office versions have e.g. the FILTER formula that can make this quite easy, but with Office 2016 you would e.g. need a manual like this one: https://www.exceldemy.com/dynamic-drop-down-list-excel-offset/ or this one: Get first non-blank value in a list

In the example below, I've created a list of projects (randomly named), added a filter box and use the value "named_list" as dropdown validation.
That name: named_list has this formula:

=OFFSET(Sheet3!$G$2;0;0;Sheet3!$H$2;1)

Book1
	A	B	C	D	E	F	G	H
1				LIST A	FILTER_OK	FILTERED_LIST	NAMES	COUNT
2				PR 248 hulking	FALSE	13	PR 163 black-and-white	2
3	filter	bl		PR 250 flow	FALSE	14	PR 202 tumble
4				PR 103 confused	FALSE	#N/A	FALSE
5	list			PR 107 jewel	FALSE	#N/A	FALSE
6				PR 127 partner	FALSE	#N/A	FALSE
7				PR 181 embarrass	FALSE	#N/A	FALSE
8				PR 388 sweater	FALSE	#N/A	FALSE
9				PR 212 hurried	FALSE	#N/A	FALSE
10				PR 269 forgetful	FALSE	#N/A	FALSE
11				PR 346 promise	FALSE	#N/A	FALSE
12				PR 369 fabulous	FALSE	#N/A	FALSE
13				PR 232 fill	FALSE	#N/A	FALSE
14				PR 163 black-and-white	PR 163 black-and-white	#N/A	FALSE
15				PR 202 tumble	PR 202 tumble	#N/A	FALSE
16				PR 309 one	FALSE	#N/A	FALSE
17				PR 241 illustrious	FALSE	#N/A	FALSE
18				PR 346 spotty	FALSE	#N/A	FALSE
19				PR 133 recondite	FALSE	#N/A	FALSE
20				PR 196 hard-to-find	FALSE	#N/A	FALSE
21				PR 198 butter	FALSE	#N/A	FALSE
Sheet3

Cell Formulas
Range	Formula
E2:E21	E2	=IF(ISERROR(SEARCH($B$3,D2,1)),FALSE,D2)
F2	F2	=MATCH(TRUE,NOT(ISNONTEXT($E$2:$E$21)),0)
G2:G21	G2	=IF(ISERROR(F2),FALSE,INDEX($D$2:$D$21,F2,1))
H2	H2	=COUNTIF(G2:G21,"<>"&FALSE)
F3:F21	F3	=MATCH(TRUE,NOT(ISNONTEXT(OFFSET($E$2:$E$21,F2,0))),0)+F2

Cells with Data Validation
Cell	Allow	Criteria
B5	List	=named_list

 

[Akuini]

@Filipzgela
See if this suits your need:
search-delist

 

[Filipzgela]

@Filipzgela
See if this suits your need:
search-delist

I cannot use any plug-ins, because of large number of users who have different versions of Excel. My solution was that it is not necessary i've made workarounds for this by giving instructions to search the second sheet to people.