Validation lists

[Max]

Hello all,
I need some help on using functions with a validation list.
There are three columns Catagory, Product and Pack size.
How I would like it to work is 1st select a catagory from a validation list,
then in the product column validation list will give a list of all the
products in that catagory, then in the pack size column the pack size list
will appear in the validation list.

This works well until I want to clear the list. If I don't work backward by
selecting the pack size and choosing blank then the product then the catagory.


I have used the following formulas to try and get the lists in the
corresponding row to show blank in the cell when I select the blank in the
Catagory validation list.

Product

=IF(ISBLANK(A$2),"",CHOOSE(MATCH($A2,Catagories,0),Spirits,Liqueurs,Aperitiefs___Digestifs,Wines____Champ,Boxed_Wines,Bottle_Beers,D_Beers,Minerals,Sundries,""))

Pack size

=IF(ISBLANK($A2),"",IF(VLOOKUP($A2,Cata01,2,FALSE)="Volume",Volume,IF(VLOOKUP($A2,Cata01,2,FALSE)="Mass",Mass,"")))

Minerals, volumes etc are range names.

If you can help please do so, and if you can suggest a better function for
the existing formulas and or method please do so.

Thank you and best regards

Max

 

[Shane Devenshire]

Hi,

Here are the steps for creating a dynamic data validation list:

1. Create three or more list in the spreadsheet. For example:
A B C
New York Quebec Canada
Seattle Toronto US
Miami Victoria

You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this
last is optional

2. Select the cell where you want the initial validation list, for this
example, D1
3. Choose Data, Validation
4. From the Allow drop-down and choose List
5. In the Source box enter the following formula
=Countries
6. Move to the location where you want the dynamic (changing) list to
appear, say for example E1
7. Choose Data, Validation, List and in the Source box enter the formula
=INDIRECT(D1)

You can also make a self-referential dynamic data validation list.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Max]

Hello Shane,
Your method works, but I need to add another list that is linked.
As in my example Catagory say Beer, Wine, Snacks, Spirits. (in cell A2)
For each Catagory list a list of Products are available in cell B2.
This in turn will allow a third cell say C1 to display in a validation list
the Pack sizes for these products.
If you select beer in cell A1. In B1 you validation list will give a
selection of various beers.
In Cell C1 you want the validation list for a Pack size, eg 33cl, or 50cl or
for the thirsty 1lt.
For Pack size there would be measures of Volume and Weight or Mass.

How do I get to linking to the third list linked to the Pack size?

Thank you

Max

 

[Max]

Hello again Shane,
You refered to making a self-referential dynamic data validation list.
Would you please expalin how I can do this?

Thank you and best regards

Max

 

[Shane Devenshire]

Hi,

You can just extend the idea used for the first dynamic validation list to
the second list, its exactly the same technique.

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi,

Let me explain this with an example: Suppose you have a list of countries
and when you pick an item from that list the list will change to show the
states in that country (in the same list). And you can redisplay the coutry
list from a state list, again not needing to move from one list to another.

1. So here are four lists in columns K,L, M & N

US Countries Countries Countries
Canada Alabama British Columbia
Mexico Alaska Quebec
Costa Rica Arizona Ontario
Arkansas
California

2. You apply range names to each as follows: Countries, US, Canada, Alabama
respectively. Notice the last list has only one element, the name of the
list from which it was picked. Here I have named that one cell Alabama. But
since you can give a cell as many names as you want you can name it in such a
way that it is the end cell for all state lists.

3. In cell A1 type the word Countries

4. In cell A1 create a data validation with the List option and the formula
=INDIRECT(A1) and click OK.

Now when you open the data validation drop down in A1 you will see the list
of countries, pick US. Now open the data validation drop down and you will
see a list of US states with Countries at the top. If you pick Countries you
will now find the countries list in the data validation drop down - you have
returned to the original list. Don't pick any state except Alabama, because
at present we haven't created any named ranges for those other states, so you
will be trapped. If all you want the user to do is pick a state then you
could create named ranges for each state which had Countries as its only
element.

If this helps, please click the Yes button

cheers,
Shane Devenshire

 

[Max]

Hello Shane,
Thank you very much, your idea has saved me some time.

Thank you for your help and patients.

Max