Dynamic Drop Down List in Excel -

my data arranged in way:

rowa     b                   
1   abc description 1
2   xyz description 2
3   mnodescription 3
4   abc description 4
5   mnodescription 5

now in worksheet in cell a1 there should drop down list based on column above. on clicking abc, xyz & mno shall shown in drop down. when select, say, abc in a1; in b1 both descriptions (description 1 & description 4) shall shown in drop down list.

i wrote function concatenates matches:

public function concatmatches(byref rgfind range, byref rgsource range, byval lngoffset long) string   dim rghit range, firstaddress string, nowrap boolean   set rghit = rgsource.find(rgfind.value)    'ensure no wrapping occurs avoid infinite loops   firstaddress = rghit.address   nowrap = true    dim concat string   while not (rghit nothing) , nowrap     if concat <> ""         concat = concat & ", "     end if     concat = concat & rghit.offset(0, lngoffset)      'find next , ensure didn't wrap first hit     set rghit = rgsource.find(rgfind.value, rghit)     nowrap = (firstaddress <> rghit.address)   wend    concatmatches = concat end function 

and function display unique values range (for data validation), enter array formula + use dynamic named range. show how use them below:

public function getuniques(rglist range) variant     'prepare return array matching calling range dimensions     dim callerrows long, callercols long, calleraddr string     dim rowndx long, colndx long, v variant     application.caller         callerrows = .rows.count         callercols = .columns.count     end     dim result() variant: redim result(1 callerrows, 1 callercols)     'fill result blank strings     rowndx = 1 callerrows         colndx = 1 callercols             result(rowndx, colndx) = ""         next colndx     next rowndx      'filter out uniques     dim dict variant: set dict = createobject("scripting.dictionary")     each v in rglist.cells         dict(v.value) = 1     next v      'push uniques first column of resulting array     rowndx = 1     each v in dict.keys()         result(rowndx, 1) = v         rowndx = rowndx + 1     next v      getuniques = result end function 

1. enter formula shown on picture below , press ctrl+shift+enter:

2. open name manager ctrl+f3 , define dynamic named range following formula =offset(sheet4!$c$2,0,0,match("*",sheet4!$c$2:$c$6,-1),1):

3. use dynamic named range list data validation:

4. works expected:

notice : array formula not dynamic values entered , may need updated match number of rows when additional rows added - remember press ctrl+shift+enter when updating range.

udf entered array formula: