Hi,
As my excel version does 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 B3 and below under Entry sheet per sample file attached.
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 B3 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 VBA 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
Lenard
As my excel version does 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 B3 and below under Entry sheet per sample file attached.
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 B3 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 VBA 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
Lenard