I have a problem that I hope someone can resolve for me. I suspect that
the solution may require VBA however my skills are quite limited.

Here is what I am trying to do:

I have created a spreadsheet with a list that a user will populate.
Within the list are a number of fields, most with drop down lists
created using data validation. As the user completes the fields running
from left to right the data validation fillters the choices the user
has to selct from. e.g the first selection is Gender: The user selects
Female, the next selection is Category, data validation only allows the
user to select categories that apply to females e.g. Skirts. I am using
a combination of ranges and indirect formulars to acheive this.

This approach works fine for most fields however:

As the user selects more and more fields they finally arrive at "style
level". They select the style from a list of around 1000 styles. The
next selection they need to make is colour. What I am trying to do is
get the data validation list to only show the colour options that are
relevant for that style; this may range from 1 to 10 different colours.
I obviously can't addopt the same approach of named ranges as I would
have to create a named range for each style that contained the colour
options in it.

Any help would be welcome. Sorry if my explanation is a bit wordy.

John

Just some thoughts--who knows if they could be implemented...

I think I'd approach this by using a Userform with a bunch of
comboboxes/listboxes. I think it would be easier to make it work.

And a worksheet that has all the products that can be chosen--one row per
product.

Column A would hold a Gender indicator (male/female)
Column B would hold a Category indicator
Column C would hold the next level indicator
....
Column X-1 would hold the Style indicator
Column X would hold the color indicator.

Then each change to a combobox/listbox would cycle through this GIGANTIC table
of options and look for the things that apply. Making sure that duplicates are
ignored.

Essentially the code would apply Data|Filter|autofilter to that range, then
filter by each column and get the list of unique values from that next column
(for the next combobox).

The bad news is that the number of lines in the flat file database can get
pretty large, pretty fast.

(Gender) * (category) * (style) * (color)
2 * 10 * 5 * 10 (for example)
ends up with 1000 rows.

==============
This sounds like a job for a real relational database. I've never used Access,
but if you have (or one of your co-workers does), then I'd consult with them
before doing anything more in excel.

In fact, you may want to post in an Access newsgroup for help.

If your company uses a different database program, find a person/newsgroup to
help.

I bet it could be done easier in a real database program.



John wrote:

I have a problem that I hope someone can resolve for me. I suspect that
the solution may require VBA however my skills are quite limited.

Here is what I am trying to do:

I have created a spreadsheet with a list that a user will populate.
Within the list are a number of fields, most with drop down lists
created using data validation. As the user completes the fields running
from left to right the data validation fillters the choices the user
has to selct from. e.g the first selection is Gender: The user selects
Female, the next selection is Category, data validation only allows the
user to select categories that apply to females e.g. Skirts. I am using
a combination of ranges and indirect formulars to acheive this.

This approach works fine for most fields however:

As the user selects more and more fields they finally arrive at "style
level". They select the style from a list of around 1000 styles. The
next selection they need to make is colour. What I am trying to do is
get the data validation list to only show the colour options that are
relevant for that style; this may range from 1 to 10 different colours.
I obviously can't addopt the same approach of named ranges as I would
have to create a named range for each style that contained the colour
options in it.

Any help would be welcome. Sorry if my explanation is a bit wordy.

John

--

Dave Peterson