I have a raw data query that is being dropped into an excel sheet and
then i want to have a seperate sheet within the workbook to have user
run reports. I want to have a data validation cell/combo box reference
the range in the raw data query and only allow distinct values to be
selectd in the drop down.

i.e. the raw data list might have:

Mike
Mike
Paul
Mike
Paul

Data Validation cell that references the above shows:

Mike
Paul

Any ideas/thoughts how to get this done?

This is not very elegant, but maybe this will work for you.

1. In Cell A1 on Sheet1 put the column header "Names," and in the
cells below that have your names (Mike, Paul, etc.). Don't put
anything below the names in this column.

2. Define the name "Names" as

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

3. Cell A1 on Sheet2 put the column header "Names". Don't put
anything manually into this column.

4. Define the name "UniquesWithHeader" as

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

5. Define the name "UniquesWithoutHeader" as

=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

6. For the cells that you apply Data Validation to, specify the Source
as "=UniquesWithoutHeader".

7. Run the code below.


Option Explicit

Sub ValidationEntries()

Dim wksCurrentSheet As Worksheet

Set wksCurrentSheet = ActiveSheet

' Clear previous list of unique names
Range("UniquesWithoutHeader").ClearContents

' Copy updated list of unique names
Range("Names").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("UniquesWithHeader"), _
CopyToRange:=Range("UniquesWithHeader"), _
Unique:=True

' Sort list of unique names
Application.ScreenUpdating = False
With Range("UniquesWithHeader")
.Parent.Select
.Sort _
Key1:=.Cells(2), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

wksCurrentSheet.Select

End Sub


Mark


JI wrote:
I have a raw data query that is being dropped into an excel sheet and
then i want to have a seperate sheet within the workbook to have user
run reports. I want to have a data validation cell/combo box reference
the range in the raw data query and only allow distinct values to be
selectd in the drop down.

i.e. the raw data list might have:

Mike
Mike
Paul
Mike
Paul

Data Validation cell that references the above shows:

Mike
Paul

Any ideas/thoughts how to get this done?