I have started a book club and made an excel sheet for all of the details
including the books owner. If a member has put on multiple books, their name
appears multiple times. What i am trying to do is generate a list with
everyones name on it only once, from the long list of repeated names. This
short list will then be linked to the drop down box, so that a new member to
the book club only has to enter their name once, then it appears in the drop
down list.

You can use Advanced Filter to do this very quickly. Debra Dalgleish
shows how he

http://www.contextures.com/xladvfilter01.html#FilterUR

Hope this helps.

Pete

On Oct 21, 12:21*am, The Fru Fru
wrote:
I have started a book club and made an excel sheet for all of the details
including the books owner. *If a member has put on multiple books, their name
appears multiple times. *What i am trying to do is generate a list with
everyones name on it only once, from the long list of repeated names. *This
short list will then be linked to the drop down box, so that a new member to
the book club only has to enter their name once, then it appears in the drop
down list.

Hi,

You can also create a pivot table for the names. This will list all the
unique names

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"The Fru Fru" wrote in message
...
I have started a book club and made an excel sheet for all of the details
including the books owner. If a member has put on multiple books, their
name
appears multiple times. What i am trying to do is generate a list with
everyones name on it only once, from the long list of repeated names.
This
short list will then be linked to the drop down box, so that a new member
to
the book club only has to enter their name once, then it appears in the
drop
down list.

An alternative formulas play, which gives full dynamic on-the-fly convenience

Assume source names will be in Sheet1, in A2 down
In another sheet,
Put in A2:
=IF(Sheet1!A2="","",IF(COUNTIF(Sheet1!A$2:A2,Sheet 1!A2)1,"",ROW()))

Put in B2:
=INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1)))
Copy A2:B2 down to cover the max expected extent of source data, say, down
to B100?

Click Insert Name Define
Name: MyR
Refers to:
=OFFSET(Sheet2!$B$2,,,SUMPRODUCT(--(NOT(ISERROR(Sheet2!$B$2:$B$100)))))

Then just go back to Sheet1,
select the source range A2:A100, apply DataValidation:
Allow: List, Source: =MyR
Click OK (Ignore the error prompt)

Test it out .. Start entering names in A2 down. As you enter, the DV will
start to populate its uniques list. If you already have names in A2 down, you
won't get the closing DV error prompt, and the DV will immediately display
the uniques list.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"The Fru Fru" wrote:
I have started a book club and made an excel sheet for all of the details
including the books owner. If a member has put on multiple books, their name
appears multiple times. What i am trying to do is generate a list with
everyones name on it only once, from the long list of repeated names. This
short list will then be linked to the drop down box, so that a new member to
the book club only has to enter their name once, then it appears in the drop
down list.

BRILLIANT! It worked great, and with a bit more thinking I could tag it
anywhere, worked perfectly first time around, no error messages. Thanks
heaps Max, oh and coffee!!

"The Fru Fru" wrote:

Thanks, but i got a bit lost, not really paying attention as i'd already done
the advanced filter above and updated it by inserting the step into a macro.
would need more time and sleep before I attack this.

"Max" wrote:

An alternative formulas play, which gives full dynamic on-the-fly convenience

Assume source names will be in Sheet1, in A2 down
In another sheet,
Put in A2:
=IF(Sheet1!A2="","",IF(COUNTIF(Sheet1!A$2:A2,Sheet 1!A2)1,"",ROW()))

Put in B2:
=INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1)))
Copy A2:B2 down to cover the max expected extent of source data, say, down
to B100?

Click Insert Name Define
Name: MyR
Refers to:
=OFFSET(Sheet2!$B$2,,,SUMPRODUCT(--(NOT(ISERROR(Sheet2!$B$2:$B$100)))))

Then just go back to Sheet1,
select the source range A2:A100, apply DataValidation:
Allow: List, Source: =MyR
Click OK (Ignore the error prompt)

Test it out .. Start entering names in A2 down. As you enter, the DV will
start to populate its uniques list. If you already have names in A2 down, you
won't get the closing DV error prompt, and the DV will immediately display
the uniques list.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"The Fru Fru" wrote:
I have started a book club and made an excel sheet for all of the details
including the books owner. If a member has put on multiple books, their name
appears multiple times. What i am trying to do is generate a list with
everyones name on it only once, from the long list of repeated names. This
short list will then be linked to the drop down box, so that a new member to
the book club only has to enter their name once, then it appears in the drop
down list.

Delighted to hear. Please take a moment to press the YES button in that
earlier response, won't you? Thanks
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"The Fru Fru" wrote:
BRILLIANT! It worked great, and with a bit more thinking I could tag it
anywhere, worked perfectly first time around, no error messages. Thanks
heaps Max, oh and coffee!!