Is this possible...Filter list after user selects location

Is this possible? I am using Excel 2003.

I have a list of all customers on worksheet named, Customers. Besides
listing customer demographics, it also includes a column to show which
company site services the customer.

The company needs to obtain further information regarding customers, which
is not demographic info. The company sites will enter this information. I
would like to have another worksheet for this purpose (I will name the
worksheet, OnCall Info). I will sent up columns to collect specific
information. I will include a column with data validation (list) for sites
to select customers from a drop down list (named range on customer
worksheet).

Since the company has over five-hundred customers, is it possible to filter
the drop down list based on the site selecting their location from a drop
down list (named worksheet range). I'm thinking I would have to require the
sites to select their location from a drop down list in a single cell on the
worksheet, OnCall Info. But how would I have the customer list show only
customers for that company site?

I don't have much experience with VBA. So if the above request would
require that, I would need help with writing the code.

TIA

Hi jan

check out http://www.contextures.com/xlDataVal02.html on using dependent
data validation lists to see if this will meet your needs.

Cheers
JulieD

"Jan" <[email protected]> wrote in message
news:[email protected]...
> Is this possible? I am using Excel 2003.
>
> I have a list of all customers on worksheet named, Customers. Besides
> listing customer demographics, it also includes a column to show which
> company site services the customer.
>
> The company needs to obtain further information regarding customers, which
> is not demographic info. The company sites will enter this information. I
> would like to have another worksheet for this purpose (I will name the
> worksheet, OnCall Info). I will sent up columns to collect specific
> information. I will include a column with data validation (list) for
> sites
> to select customers from a drop down list (named range on customer
> worksheet).
>
> Since the company has over five-hundred customers, is it possible to
> filter
> the drop down list based on the site selecting their location from a drop
> down list (named worksheet range). I'm thinking I would have to require
> the
> sites to select their location from a drop down list in a single cell on
> the
> worksheet, OnCall Info. But how would I have the customer list show only
> customers for that company site?
>
> I don't have much experience with VBA. So if the above request would
> require that, I would need help with writing the code.
>
> TIA
>

Julie,
I checked out the site and reviewed the examples. However, the examples
make sense for the data examples it shows; Short lists with a few
alternatives based on what was selected initially. Based on the example, I
would have to create separate lists for each site's customers.

I already have a worksheet to use as data validation for company site.
My customer list is contiguious and contains over 500 customers; the company
site is listed more than once; list is sorted by company site and customer
name. Columns for customer list are: company site, bu, customer name,
address....., i.e.

Wichita, 45014, ABC, 123 uphill
Omaha, 45144, XYZ, 987 downhill



"JulieD" wrote:

> Hi jan
>
> check out http://www.contextures.com/xlDataVal02.html on using dependent
> data validation lists to see if this will meet your needs.
>
> Cheers
> JulieD
>
> "Jan" <[email protected]> wrote in message
> news:[email protected]...
> > Is this possible? I am using Excel 2003.
> >
> > I have a list of all customers on worksheet named, Customers. Besides
> > listing customer demographics, it also includes a column to show which
> > company site services the customer.
> >
> > The company needs to obtain further information regarding customers, which
> > is not demographic info. The company sites will enter this information. I
> > would like to have another worksheet for this purpose (I will name the
> > worksheet, OnCall Info). I will sent up columns to collect specific
> > information. I will include a column with data validation (list) for
> > sites
> > to select customers from a drop down list (named range on customer
> > worksheet).
> >
> > Since the company has over five-hundred customers, is it possible to
> > filter
> > the drop down list based on the site selecting their location from a drop
> > down list (named worksheet range). I'm thinking I would have to require
> > the
> > sites to select their location from a drop down list in a single cell on
> > the
> > worksheet, OnCall Info. But how would I have the customer list show only
> > customers for that company site?
> >
> > I don't have much experience with VBA. So if the above request would
> > require that, I would need help with writing the code.
> >
> > TIA
> >
>
>
>

Assuming the list is always sorted by company site, you can create the
dependent list by using an OFFSET formula in the data validation source.
For example:

=OFFSET($B$2,MATCH(E3,$A$2:$A$2000,0)-1,0,COUNTIF($A$2:$A$2000,E3),1)

There's a sample file here:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'Dependent Cell List Offset'

Jan wrote:
> Julie,
> I checked out the site and reviewed the examples. However, the examples
> make sense for the data examples it shows; Short lists with a few
> alternatives based on what was selected initially. Based on the example, I
> would have to create separate lists for each site's customers.
>
> I already have a worksheet to use as data validation for company site.
> My customer list is contiguious and contains over 500 customers; the company
> site is listed more than once; list is sorted by company site and customer
> name. Columns for customer list are: company site, bu, customer name,
> address....., i.e.
>
> Wichita, 45014, ABC, 123 uphill
> Omaha, 45144, XYZ, 987 downhill
>
>
>
> "JulieD" wrote:
>
>
>>Hi jan
>>
>>check out http://www.contextures.com/xlDataVal02.html on using dependent
>>data validation lists to see if this will meet your needs.
>>
>>Cheers
>>JulieD
>>
>>"Jan" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>Is this possible? I am using Excel 2003.
>>>
>>>I have a list of all customers on worksheet named, Customers. Besides
>>>listing customer demographics, it also includes a column to show which
>>>company site services the customer.
>>>
>>>The company needs to obtain further information regarding customers, which
>>>is not demographic info. The company sites will enter this information. I
>>>would like to have another worksheet for this purpose (I will name the
>>>worksheet, OnCall Info). I will sent up columns to collect specific
>>>information. I will include a column with data validation (list) for
>>>sites
>>>to select customers from a drop down list (named range on customer
>>>worksheet).
>>>
>>>Since the company has over five-hundred customers, is it possible to
>>>filter
>>>the drop down list based on the site selecting their location from a drop
>>>down list (named worksheet range). I'm thinking I would have to require
>>>the
>>>sites to select their location from a drop down list in a single cell on
>>>the
>>>worksheet, OnCall Info. But how would I have the customer list show only
>>>customers for that company site?
>>>
>>>I don't have much experience with VBA. So if the above request would
>>>require that, I would need help with writing the code.
>>>
>>>TIA
>>>
>>
>>
>>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html