Validate List from non-Adjacent cells

**ElmerS** · 10-29-2009, 01:41 PM

Hi,
My question is explained inside the attached WB.
The items of the list, in cell C8, should be adjacent (no gaps)
Thanks, Elm

**DonkeyOte** · 10-31-2009, 05:17 AM

ElmerS, the attachments in your posts should support the narrative in your post and not vice versa - please ask your question within the post rather than the supporting document(s).

Using helpers is the most sensible approach here regardless of preference.

For ex.

B2: =INDEX($A$2:$A$14,MATCH(1,INDEX(($A$2:$A$14<>"")*ISNA(MATCH($A$2:$A$14,B$1:B1,0)),0),0))
copied down to say B14

Then use a Dynamic Named Range

Name: _dvList
RefersTo: =OFFSET($B$2,,,COUNTIF($B$2:$B$14,"*?"),1)

Then set Validation List source to be = _dvList

Thus as you alter content of A2:A14 so your Data Validation list will alter accordingly.

**ElmerS** · 10-31-2009, 05:46 AM

In that case I prefer the VBA approach:

For Each CL In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    If CL <> "" Then LST = LST & CL & ","
Next Test
With [C8].Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:=LST
End With

Thanks anyway,

**DonkeyOte** · 10-31-2009, 05:56 AM

No problem though from your prior posts I always assumed you had a complete aversion to VBA given the insistence on formulae resolution (even in the face of inefficiency).

If using VBA it would probably make sense to apply it to a Change event against A2:Ax given alterations on those cells should in turn affect the validation list itself... however ignoring that for the being...

-- using [C8] is generally slower than using Range("C8")

-- per your pseudo-code Test should read CL

-- if as assumed above A2:Ax are constants you can further limit the iteration by using SpecialCells - ie ignore blanks in the first instance

Dim CL As Range, LST As String
On Error Resume Next
For Each CL In Range(Cells(2,"A"),Cells(Rows.Count,"A")).End(xlup).SpecialCells(xlCellTypeConstants).Cells
  LST = LST & "," & CL.Value
Next CL
With Range("C8").Validation
    .Delete
    .Add Type:=xlValidateList, AlterStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Replace(LST,1,1,"")
End With

**ElmerS** · 10-31-2009, 06:42 AM

Thanks,

No - no "aversion" against VBA and/or Helper columns but I prefer not to use them UNLESS there is no other solution.

As I rely on your judgment & Excel knowledge and after reading your first reply, to this question, I wrote this short piece of code.

Elm