dynamic list to remove duplicates and spaces ?

[Keith]

Hi,

I have a drop-down box I'd like to fill with unique, alphabetized
names sourced from another sheet. However, the list of names on the
other sheet contains duplicate name entries and varies in length as
the list is updated.

Currently I reference the entire column that contains the names, which
results in duplicate entries, and many many spaces.

Is there a way to dynamically reference this list so that the
drop-down doesn't have any duplicates or spaces? Preferably I'd like
to do this in the ListFillRange property of the combobox, and without
any VB code.

Thanks,

Keith

 

[Tom Ogilvy]

To do what you want, you would need to use complex formulas to build you
list of unique, alphabatized names and then probably a defined name to pick
up that new list.

I assume you can figure out how to do that.

 

[Gjones]

Hi Keith;

Take the column for the sort list and select one of the
cells. The use the code

ActiveCell.EntireColumn.Select

Then filter the list to some where else maybe even a
different sheet with the unique values using this code.

Range(Selection.Address).AdvancedFilter
Action:=xlFilterCopy, CopyToRange:=Range("G1" _
), Unique:=True

You can change the CopyToRange to be the sheet and range
you want

Then set the combo box to read the CopyToRange

Thanks,

Greg

 

[Keith]

Greg and Tom,

These suggestions look good, though Tom, your assumption was wrong.
I'm not so adept at excel formulas.

Greg, your recommendation looks good. Two questions:

1) I will add more data to the list, so its length will increase.
This means setting the list to equal the CopyToRange won't work since
it needs to be dynamically sized. Any recommendations?

2) Is there any way I can create this list only using excel formulas?
What if all I need to do is set the Name list range dynamically. By
this I mean I have a column of data that will grow with time and I
want to set the Name list range to be A1:A(last cell with data).

Thanks again and please let me know if anything needs clarification.

Keith

 

[Norman Jones]

Hi Keith,

See Debra Dalgleish's tutorial on dynamic ranges:


http://www.contextures.com/xlNames01.html#Dynamic


---
Regards,
Norman

1) I will add more data to the list, so its length will increase.
This means setting the list to equal the CopyToRange won't work since
it needs to be dynamically sized. Any recommendations?

2) Is there any way I can create this list only using excel formulas?
What if all I need to do is set the Name list range dynamically. By
this I mean I have a column of data that will grow with time and I
want to set the Name list range to be A1:A(last cell with data).

<Snip>