select from a fragmented list
 

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?

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

--
Regards
Frank Kabel
Frankfurt, Germany

"AndrewB" schrieb im Newsbeitrag
...
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?

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


On Thu, 2 Dec 2004 23:05:07 +0100, "Frank Kabel"
wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"AndrewB" schrieb im Newsbeitrag
...
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?

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?



"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"AndrewB" schrieb im Newsbeitrag
...
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?

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

"Gord Dibben" wrote:

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


On Thu, 2 Dec 2004 23:05:07 +0100, "Frank Kabel"
wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"AndrewB" schrieb im Newsbeitrag
...
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?

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

--
Regards
Frank Kabel
Frankfurt, Germany

"AndrewB" schrieb im Newsbeitrag
...
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?



"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"AndrewB" schrieb im
Newsbeitrag
...
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?

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

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"AndrewB" schrieb im Newsbeitrag
...
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?



"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"AndrewB" schrieb im
Newsbeitrag
...
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?