Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a name from a list of employees using Excel | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
select unique to make list | Excel Worksheet Functions | |||
Dependent List Query | Excel Worksheet Functions |