select from a fragmented list

[AndrewB]

how to I limit the data in a drop down list?
ie.
column A has a list of site names, interspersed with month names.
eg. a1 = "Agnew Bros", a2= "Jan", a3="Feb",a4= "Opunake", etc

I've been using a data validation list to pick from, but get all the month
names listed in the pick list.

How do list only the site names in the pick list?

 

[Frank Kabel]

Hi
i would create a separate list. e.g. enter the following in B1
=OFFSET($A$1,(ROW()-1)*2,0)
and copy this down
Now use column B as source for your data validation list

 

[Gord Dibben]

Alternative......if not too many!

Type the site names into the List dialog in DV.

Agnew Bros,Opunake, etc.

Note the commas must be entered.

Gord Dibben Excel MVP

 

[AndrewB]

Hi Frank,
Thanks, but it didn't quite work out.
There are normally 15 rows between each site name. ie sites names are found
in rows 1,17,33,49,63... and the list is frequently extended.

Is there a way to extract the site names only to a new, contiguous, list?

 

[AndrewB]

Unfortunately the list has 60 names, currently, and is frequently updated.

 

[Frank Kabel]

Hi
if it is always 15 rows in between try:
=OFFSET($A$1,(ROW()-1)*16,0)
in a helper column

 

[AndrewB]

Spot On! thanks...
The guy who makes the list always has a 15 rows gap.

I just came up with an alternative in macro form while I was racking by
brain (I'm not the tidiest of programmers):

i = 1
For j = 1 To 2000
k = Range("A" & j).Value
Select Case k
Case "", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
i = i
Case Else
Range("K" & i) = k
i = i + 1
End Select
Next j
End Sub

thanks again