Dynamic named range, ignoring duplicates and blanks, WITHOUT VBA

Hello,

I believe also, that this can be done, I remember reading something about this among the tips and tricks in the attached file in
http://www.excelforum.com/tips-and-t...e-learned.html
Also, can you provide a sample file? It will be easier for others to help you also.

Basing only on Tip no.2 (You can see it on worksheet "2"), you can do this.
I used the formula in the Tips and Tricks, and change some Cell Reference, and this is what I got. See attached file.
To avoid error, I put an IFERROR in front of it.

Originally Posted by aimanti

Most importantly I want to do this WITHOUT VBA and WITHOUT helper columns, using only one formula in the Named Range manager.

Can't be done that way.

Without using VBA you would have to use formulas to extract the unique items to another range and then use that other range for whatever.

I'm willing to bet you any amount of money (that you can afford to lose!) that this can not be done without using VBA without using a "helper" column to first extract the unique items.

Name your amount!

Please check the post #4 for a sample file. I think it answers your question.

Originally Posted by aimanti

Could you explain why it's impossible?

Because there isn't a single formula that will return the complete array of unique items (without empty elements).

Trust me, I've been doing this "stuff" for many years!

You mean something like this?

Fair enough -- that does get to the basic idea of what I'm looking for. Another question, though one that's less functionally important: is there a way to change the syntax of the function to reflect the Table and Column names? For example, the formula currently includes:

Formula:

=IFERROR(INDEX($J$3:$J$9,MATCH(0,COUNTIF($N$2:N8,$J$3:$J$9),0)),"")

I know that I can at least do this much to the formula, and get the same result:

Formula:

=IFERROR(INDEX(Project_Database[Team],MATCH(0,COUNTIF($N$2:N2,Project_Database[Team]),0)),"")

Is there a way to change the syntax of the $N$2:N2 range so that it uses the same Table format? I'm asking for two reasons: (a) I need to make this spreadsheet "fool proof" (i.e. boss proof), and thus need to make all formulas as easily readable as they can be, and (b) to more thoroughly understand your approach.



[EDIT: I didn't see Tony's previous post before I wrote this in my response:]


I'm still open to any takers who think they can solve the original question. I think this is a case like the famous "unsolvable statistics problem" -- using regular lines of thinking we might not arrive at the answer, but with some fancy footwork we can solve what we once thought to be unsolvable.

Otherwise, I'd love to hear an explanation/description of why it's impossible. Does it have to do with the way that ranges look up data, store it, etc? Genuinely curious, as I think it would help expand my understanding (and anyone else who is pursuing the same question, which I've seen asked in a number of places).

The $N$2:$N2 part is used to exclude any duplicated results. The formula is trying to match 0 with the COUNTIF of all possible results among the already found result, which is $N$2:$N2 in the attach file in post #10.
This range will vary largely depending on where the formula is placed, say in Column X row 3 then it will become $X$2:$X2; column Y row 10 then it will be $Y$9:$Y9.