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,
Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
But thats nothing compared to the horrors that lurk beneath The Trap Door.
Record a Macro using Advanced Filter to create a unique list copied to your desided location.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thanks,
Ok... how do I use the macro that was created in my Data Validation?
[vba]Sub AdvFilter()
Range("B3:B1001").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("B3:B1001"), Unique:=True
ActiveWindow.SmallScroll Down:=-8
End Sub[/vba]
Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
But thats nothing compared to the horrors that lurk beneath The Trap Door.
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?
Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
But thats nothing compared to the horrors that lurk beneath The Trap Door.
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.
Last edited by mdmackillop; 10-31-2008 at 09:18 AM. Reason: Answer expanded.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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,
Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
But thats nothing compared to the horrors that lurk beneath The Trap Door.
Posting Permissions
Reply With Quote