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

"Max" wrote:

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

"Shane Devenshire" wrote:

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" wrote:

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,Su ndries,""))

Pack size

=IF(ISBLANK($A2),"",IF(VLOOKUP($A2,Cata01,2,FALSE) ="Volume",Volume,IF(VLOOKUP($A2,Cata01,2,FALSE)="M ass",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