Hi there,
A month or two ago I found the solution to this problem, but now that I'm tackling it again it seems that the entire internet has forgotten how it's done. I'm hoping that's not the case here.

I have a very simple problem: I have a table ("Project_Database"), and in that table there are various columns. One of those columns ("Team") has text values. There are duplicates of some of these values (i.e. "Marketing" and "Business Development" come up more than once).

I want to create a dynamic named range ("Range") from the data in column "Team". I want "Range" to ignore duplicates and ignore blanks. Most importantly I want to do this WITHOUT VBA and WITHOUT helper columns, using only one formula in the Named Range manager.



Many forum threads I've looked at said this was impossible -- but, given that I pulled it off once in the past, I know for a fact this is not the case. From what I remember the last time I did this: the data in the table column needs to be sorted, so that all repeat instances are clustered together. The formula I used then had some mixture of INDEX, OFFSET, MATCH, though I don't remember exactly what functions and in exactly what order.

PLEASE ANY HELP WOULD BE SO MUCH APPRECIATED. I see this question asked over and over on google, yet no one has offered up a good solution yet.