Dynamic List & Named Ranges

[DipDip]

Hi all,
Just wondering if someone can help me with something. I have the following data:
[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[/TR]
[TR]
[TD]f[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]t[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
[TR]
[TD]t[/TD]
[/TR]
[TR]
[TD]d[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]e[/TD]
[/TR]
[TR]
[TD]f[/TD]
[/TR]
</tbody>[/TABLE]
What I want is for a list to be populated elsewhere in the worksheet which gives me only unique values.

I have used the following formula:

{=INDEX(List,MATCH(SMALL(IF(COUNTIF($E$1:E1,List)=0,COUNTIF(List,"<"&List),""),1),COUNTIF(List,"<"&List),0))}

BUT the range I have for the named data List is as follows:

=OFFSET(Data!$A$2, 0, 0, COUNTA(Data!$A:$A)-1,1)

Because I need the list to be dynamic for a Userform combobox I have created that uses this list and also as more data is added to Column A, I don't want to be having to alter the code all the time.

These two combinations don't work, all it does is give me the first value.

Can anyone please help me?

Thanks in advance peeps!

 

[Marcelo Branco]

There is nothing entered below A10. I must be missing something somewhere. Just not sure where. Let me quickly try on a new sheet as opposed to the one I have been working on... BRB

I have gotten your results ("a" on every cell) with a blank cell in the middle of your data. So, please check carefully.

M.

 

[Marcelo Branco]

Okay so it now works, so must be something to do with my old workbook. Cheers for the help!!! Greatly Appreciated!

Great! You are welcome.

M.