Column C in my spreadsheet contains values that will be client-chosen and frequently updated. I want column D to have data validation applied dynamically that pulls from that list. However, it needs to contain alphabetically ordered, unique values.
What I am currently doing is using the following formula to alphabetically order those values in a hidden column (BK). (Note: the site I found this on indicated it should only show unique values, however it did not).
{=INDEX(List,MATCH(0,IF(MAX(NOT(COUNTIF($BK$15:BK15,List))*(COUNTIF(List,">"&List)+1))=(COUNTIF(List,">"&List)+1),0,1),0))}
To update column D dynamically, I am using the following code:
This seems to work, except every time I click in a cell in column D it still throws a pop up box called "Remove Duplicates" that shows two checked checkboxes -- "Select All" and "Column BL". It also tells me how many duplicates were found and how many unique values will remain.
I am at a loss for why displayalerts=false hasn't turned this off, but it definitely isn't an option to have this fire every time someone clicks in column D. Has anyone seen this before? (I am on Excel for Mac 2016 by the way).
I am of course open to simpler ways of doing this if I am making this more complicated than it needs to be.
What I am currently doing is using the following formula to alphabetically order those values in a hidden column (BK). (Note: the site I found this on indicated it should only show unique values, however it did not).
{=INDEX(List,MATCH(0,IF(MAX(NOT(COUNTIF($BK$15:BK15,List))*(COUNTIF(List,">"&List)+1))=(COUNTIF(List,">"&List)+1),0,1),0))}
To update column D dynamically, I am using the following code:
Code:
Dim NewRng As Range
Dim RefList As Range, c As Range, rngHeaders As Range, RefList2 As Range, msg
On Error GoTo ErrHandling
Set NewRng = Application.Intersect(Me.Range("D16:D601"), Target)
If Not NewRng Is Nothing Then
Set rngHeaders = Range("A15:ZZ16").Find("Status List", After:=Range("E15"))
Set RefList = Range(rngHeaders.Offset(1, 0).Address, rngHeaders.Offset(100, 0).Address)
RefList.Copy
RefList.Offset(0, 1).PasteSpecial xlPasteValues
Set RefList2 = RefList.Offset(0, 1)
Application.DisplayAlerts = False
RefList2.RemoveDuplicates Columns:=Array(1), Header:=xlNo
For Each c In NewRng
c.Validation.Delete
c.Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="=" & RefList2.Address
Next c
End If
Application.DisplayAlerts = True
Application.EnableEvents = True
This seems to work, except every time I click in a cell in column D it still throws a pop up box called "Remove Duplicates" that shows two checked checkboxes -- "Select All" and "Column BL". It also tells me how many duplicates were found and how many unique values will remain.
I am at a loss for why displayalerts=false hasn't turned this off, but it definitely isn't an option to have this fire every time someone clicks in column D. Has anyone seen this before? (I am on Excel for Mac 2016 by the way).
I am of course open to simpler ways of doing this if I am making this more complicated than it needs to be.