On Wed, 19 Oct 2005 15:33:04 -0700, "washdcjohn"
wrote:

restated with more clarification;

How do I referrence source data on another worksheet where the data is
defined in a named range?

"Domenic" wrote:

Assuming that A2:A10 contains your data...

B2:

=SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&""))

C2, copied down:

=IF(ROWS($C$1:C1)<=$B$2,INDEX(A2:$A$10,MATCH(0,COU NTIF($C$1:C1,A2:$A$10),
0)),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article . com,
"Matt" wrote:

I think my issue is best displayed with an example:


Here's my rows of data:


Column A
Bob
Dave
Dave
Eric
Simon
Simon
Simon
Simon
Tom
etc....

Obviously there's other columns of data associated with each person.


Is there a formula that can return the following:
Bob
Dave
Eric
Simon
Tom
etc...


i.e. only the unique fields from the array?

I want to return the unique fields on a different sheet and then use
sumif on them to get the required results. I can't realy use
filters/advanced filters etc as I want the user to only have to paste
in his appropriate data and then move to the other sheet and see his
results.

Can anyone help?

Thanks
Matt



You could download Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ and use the UNIQUEVALUES function.



--ron