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

[Guest]

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

 

[JulieD]

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

 

[Guest]

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

 

[Debra Dalgleish]

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'