If you have a large list of names then you need to create a
consolidated list of names some place in the workbook. You can place
the names in column IV or a hiden column on one of the worksheets.

I think the easiest way is to create a macro that combines the names
and creates a validation list in the last sheet. You would need to run
the macro every time a new name is added but it would be very simple to
adds rows to the last sheet.

What I usually do is to add all the names to one column. Then use
advance filter method to get a lists of unique names.

Try this macro below

Sub MakeValidationList()

Set Sumsht = Sheets("Summary")

For Each sht In Sheets
If UCase(sht.Name) < "SUMMARY" Then
'copy data to column IU on summary sheet
With sht
'get range of names on sht in column A
'Assume header row so data starts in row 2
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set DataRange = .Range("A2:A" & LastRow)
End With

With Sumsht
'get last row of data in column IU
If .Range("IU1") = "" Then
'no names in summary sheet
'put data in header row
'so advance filter works properly
.Range("IU1") = "Names"
End If

LastRow = .Range("IU" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

'paste names into column
DataRange.Copy _
Destination:=.Range("IU" & NewRow)

End With
End If

Next sht

With Sumsht

'get unique names
LastRow = .Range("IU" & Rows.Count).End(xlUp).Row
.Range("IU1:IU" & LastRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), _
Unique:=True

'delete temprary data in column IU
.Columns("IU").Clear

LastRow = .Range("IV" & Rows.Count).End(xlUp).Row
Set ValidationNames = .Range("IV2:IV" & LastRow)

'create a validation list in column A in summary sheet
'make the validation range 1000 rows after last data
'so workbook doesn't grow vary large
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
LastRow = LastRow + 1000

'assume header row in column A
Set ValidationRange = .Range("A2:A" & LastRow)

With ValidationRange.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & ValidationNames.Address
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=204450

http://www.thecodecage.com/forumz