PDA

View Full Version : Solved: Data Validation - Avoid Duplicate Entries



phendrena
10-31-2008, 06:15 AM
Hi there,

I have created a name range called "Month".
I want to use this in a data validation but need to avoid it listing duplicate entries. Is there away to use this name range in data validation without having it show duplicates as the data in the Month column can repeat itself many times.

I'd appreciate either vba or non-vba solutions.

Thanks,

mdmackillop
10-31-2008, 06:47 AM
Record a Macro using Advanced Filter to create a unique list copied to your desided location.

phendrena
10-31-2008, 07:45 AM
Thanks,

Ok... how do I use the macro that was created in my Data Validation?

Sub AdvFilter()
Range("B3:B1001").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("B3:B1001"), Unique:=True
ActiveWindow.SmallScroll Down:=-8
End Sub

phendrena
10-31-2008, 07:51 AM
Thanks for the reply,

I understand what your saying now (re-read it slower!)

Use the advance filter options to crete a unique list elsewhere and reference that list.
With that in mind i'd therefore need to keep updating the list which isn't practical.

So i'm left with the original question, how can i avoid duplicate entries in a Data Validation List?

mdmackillop
10-31-2008, 09:15 AM
Any such list needs to be refreshed from current data. You can trigger a macro to do this by any event from opening a workbook to changing cell selection or changing cell data. In this case it could be when you select the cell for data entry, or change the source data.

phendrena
10-31-2008, 09:58 AM
Thanks,

It's a shame that this can't be done any other way as i could end up with several seperate massive lists. However, it is a solution and one that I can use.

Thanks,