First, where ever you enter your formula make sure that there's an empty
cell above it. So, for example, if you were going to generate your list
in Column B...

1) Leave B1 empty

2) Enter the following formula in B2 and copy down:

=IF(OR(COUNTIF($B$1:B1,List)=0),INDEX(List,MATCH(0 ,COUNTIF($B$1:B1,List),
0)),"")

....where 'List' is your named range. Adjust this reference accordingly.
The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!

In article ,
"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