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]

In which cell are you entering the first formula?
Putting it on E2 and copying it down everything worked perfectly - see below


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td][/td][td][/td][td][/td][td]

Unique List​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

f​

[/td][td][/td][td][/td][td][/td][td]

a​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

a​

[/td][td][/td][td][/td][td][/td][td]

c​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

t​

[/td][td][/td][td][/td][td][/td][td]

d​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

c​

[/td][td][/td][td][/td][td][/td][td]

e​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

t​

[/td][td][/td][td][/td][td][/td][td]

f​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

d​

[/td][td][/td][td][/td][td][/td][td]

t​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

a​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

e​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

f​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Note that I wrapped the formula with the IFERROR (...) function to avoid errors.

Array formula in E2 copied down
=IFERROR(INDEX(List,MATCH(SMALL(IF(COUNTIF($E$1:E1,List)=0,COUNTIF(List,"<"&List),""),1),COUNTIF(List,"<"&List),0)),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[DipDip]

Did you use the following for the list? As i have just tried what you suggested and it just displays "a" in every cell. Which is the problem I was getting. I was pressing ctrl+shift+enter, to set as an array.

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

 

[Marcelo Branco]

Yes, i used the dynamic named range and everything worked perfectly.

You didn't answer my question.
In which cell(s) are you entering the formula(s)?

M.

 

[DipDip]

Sorry, I am entering it in E2 as you have. This is annoying then. Not sure why it won't work.

 

[Marcelo Branco]

Sorry, I am entering it in E2 as you have. This is annoying then. Not sure why it won't work.

So, i don't understand why is not working
What Excel version are you using?

M.

 

[Marcelo Branco]

Ah, it seems that you have more data below the last entry or blank cells in the middle of your data.
Is it the case?

M.

 

[DipDip]

So, i don't understand why is not working
What Excel version are you using?

M.

I'm running Excel 2016 MSO

 

[DipDip]

Ah, it seems that you have more data below the last entry or blank cells in the middle of your data.
Is it the case?

M.

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

 

[DipDip]

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