Searchable Drop Down List in Data Validation without using VBA

Lenard

New Member
Joined
Jan 19, 2010
Messages
35
Office Version
  1. 2019
Platform
  1. Windows
Hi

As my excel version do not have filter, sort, unique functions I have to use other set of formulas to search the drop down list in data validation.
A range of data is created with Code and BP name in column A & B under Master sheet where "Master" name range is set on column B and Data validation list is set without error alert at cell B2 and below under Entry sheet

MrExcel_Searchable DropDownList Data Validation.xlsx
BC
2BP NAMECODE
3CD Trading LtdAP-C006
4Trad 
5Co 
6 
7 
8 
9 
10 
Entry
Cell Formulas
RangeFormula
C3:C10C3=IFERROR(INDEX(Master!A:A,MATCH(Entry!B3,Master!B:B,0),1),"")
Named Ranges
NameRefers ToCells
Master!_FilterDatabase=Master!$A$1:$B$1C3:C10
Master=OFFSET(Master!$B$2,,,COUNTA(Master!$B:$B)-1)C3:C10
Cells with Data Validation
CellAllowCriteria
B3:B10List=OFFSET(Master!$B$2,MATCH($B3&"*",Master,0)-1,,COUNTIF(Master,$B3&"*"),)


MrExcel_Searchable DropDownList Data Validation.xlsx
ABC
1 CODE BP NAME
2AP-C001ABC Ltd
3AP-C002Allan Fong Bus Terminal
4AP-C003BCD Enterprise
5AP-C004CCD Co
6AP-C005CCD Pte Ltd
7AP-C006CD Trading Ltd
8AP-C007Coco Farm Enterprise
9AP-C008CoConet Wholesaler
10AP-C009Dast Hardware Shop
11AP-C010DFG Engineering Co
12AP-C011Farming Tree Garden
13AP-C012Fire Fox Construction
14AP-C013Flex Dasta Construction
15AP-C014LightDelta Distributor
16AP-C015Mass Com Production
17AP-C016Mass Global Network
18AP-C017PSP Distributor Co
19AP-C018QQ Ylang Wholesale
20AP-C019Rar Pile Trading Co
21AP-C020Wass Entertainment Show
22
Master



Below set of formulas copied from google search and modified :-
1) Master name range "OFFSET(Master!$B$2,,,COUNTA(Master!$B:$B)-1)"
2) Data Validation List "OFFSET(Master!$B$2,MATCH($B3&"*",Master,0)-1,,COUNTIF(Master,$B3&"*"),)"

When new BP name & code are added to the list of Master sheet, the list will be updated and sorted automatically in data validation list.
If there are more than 100 names to search in drop down list, it would be more efficient to use dynamic search in drop down list.
However, when I try to do dynamic search by "Trad" or "Co" in cell B2 in Entry sheet, the drop down list show nothing ??

Did I miss out anything in my formulas ?

My objective is to make every row of data entry as dynamic search in drop down list data validation such as filtering search without using VBA and also do not use filter, sort, unique function as they work on office 365 and it will be refresh the drop down list for next row of entry.

Appreciate if anyone can help to solve the above problem and thanks in advance

Regards
Len
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,223,879
Messages
6,175,142
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top