# Table column as range name

#### mark hansen

##### Well-known Member
I'm trying to pull a unique list of names, in alphabetical order from a column in a table.

I picked a very helpful formula from Andrew Poulsom regarding getting a unique list of name in alphabetical order and it works great... The thread is"

I modified the formula to:
{=IFERROR(INDEX(UniqueShortNurseList,MATCH(0,COUNTIF(UniqueShortNurseList,"<"&UniqueShortNurseList)-SUM(COUNTIF(UniqueShortNurseList,"="&Z\$35:Z35)),0)),"")}
And it works fine

UniqueShortNurseList is a range pulled from my table using this formula:
{=IF(IFERROR(INDEX(Table1[User Name sending data],MATCH(0,COUNTIF(AA\$34:\$AA34,Table1[User Name sending data]),0)),"")=0,"",IFERROR(INDEX(Table1[User Name sending data],MATCH(0,COUNTIF(AA\$34:\$AA34,Table1[User Name sending data]),0)),""))}

The second formula doesn't put the list in alphabetical order. (That's what I did first, being lazy, but the client wanted them in alpha order... Don't blame them, the list will get long)

So the first formula uses the list create by the second formula sort of as a helper list... But I'm thinking, I don't need to do this... I thinking (hoping) I can do this:
{=IFERROR(INDEX(Table1[User Name sending data],MATCH(0,COUNTIF(Table1[User Name sending data],"<"&Table1[User Name sending data])-SUM(COUNTIF(Table1[User Name sending data],"="&Z\$35:Z35)),0)),"")}

But using the "Table1[User Name sending data]" as the range name doesn't seem to work.

All are entered with CTRL+SHIFT+ENTER

Am I barking up the wrong tree?
Thanks for any insight.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Fluff

##### MrExcel MVP, Moderator
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

#### mark hansen

##### Well-known Member
Thanks, we are using Office 2016 but may be going to 365 in a few months.

#### Fluff

##### MrExcel MVP, Moderator
Thanks for that, your final formula should work. Is it in Z36 dragged down?
Also do you have any blank cells in the table?

#### mark hansen

##### Well-known Member
The formula is pasted into Z36 and entered with CTRL+SHIFT+Enter and dragged down. I did have a blank cell in that column and when I filled that in, it worked... I didn't even look for blank cells.

I appreciate the help.

I didn't realize a blank cell would affect the formulas with the table address. In doing some testing, I would erase a cell in that column, and the last formula with the Table address didn't like the blank cell... The other two formulas were not affected.

Thanks again for your help and insight
Mark

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
3
Views
287
Replies
26
Views
377
Replies
8
Views
747
Replies
2
Views
227
Replies
8
Views
96

1,130,091
Messages
5,640,059
Members
417,125
Latest member
sfreind

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back