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
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
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 | ||||
---|---|---|---|---|
B | C | |||
2 | BP NAME | CODE | ||
3 | CD Trading Ltd | AP-C006 | ||
4 | Trad | |||
5 | Co | |||
6 | ||||
7 | ||||
8 | ||||
9 | ||||
10 | ||||
Entry |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3:C10 | C3 | =IFERROR(INDEX(Master!A:A,MATCH(Entry!B3,Master!B:B,0),1),"") |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Master!_FilterDatabase | =Master!$A$1:$B$1 | C3:C10 |
Master | =OFFSET(Master!$B$2,,,COUNTA(Master!$B:$B)-1) | C3:C10 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B3:B10 | List | =OFFSET(Master!$B$2,MATCH($B3&"*",Master,0)-1,,COUNTIF(Master,$B3&"*"),) |
MrExcel_Searchable DropDownList Data Validation.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | CODE | BP NAME | |||
2 | AP-C001 | ABC Ltd | |||
3 | AP-C002 | Allan Fong Bus Terminal | |||
4 | AP-C003 | BCD Enterprise | |||
5 | AP-C004 | CCD Co | |||
6 | AP-C005 | CCD Pte Ltd | |||
7 | AP-C006 | CD Trading Ltd | |||
8 | AP-C007 | Coco Farm Enterprise | |||
9 | AP-C008 | CoConet Wholesaler | |||
10 | AP-C009 | Dast Hardware Shop | |||
11 | AP-C010 | DFG Engineering Co | |||
12 | AP-C011 | Farming Tree Garden | |||
13 | AP-C012 | Fire Fox Construction | |||
14 | AP-C013 | Flex Dasta Construction | |||
15 | AP-C014 | LightDelta Distributor | |||
16 | AP-C015 | Mass Com Production | |||
17 | AP-C016 | Mass Global Network | |||
18 | AP-C017 | PSP Distributor Co | |||
19 | AP-C018 | QQ Ylang Wholesale | |||
20 | AP-C019 | Rar Pile Trading Co | |||
21 | AP-C020 | Wass 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