Data Validation - Multiple Dependent Lists

[Lisa C.]

I am trying to create 3 dependent data validation lists. Each Chain has a
dependent list of Products. Each Product has a dependent list of Displays.
Any of the lists may contain multiple word phrases. I've already looke at
the Contextures.com website and it didn't help. It only provides examples of
two word phrases.

Lists Names:

Chains CVSOdyProducts
CardStockDisplays
A B
C
1 CVS Odyssey Card Stock 4' In-Line
2 Kohls Traditional Gift Wrap Supplies 6' In-Line
3 Walgreens ACE Boxes Notes & Stationery 3' Aisle

Form:
A
Choose Chain: 1 CVS Odyssey
Choose Product: 2 Card Stock
Choose Display: 3 3' Aisle

I need the data validation formula for cells A2 and A3 on the form. Thanks!

 

[Ed Ferrero]

Hi Lisa,

Go to http://www.edferrero.com/ExcelCharts/tabid/102/Default.aspx
and download 'Cascading Cell Validation' (page 2, I think)

That sample has 2 dependent levels, plus an explanation. To see a sample
with 3 dependent levels, download
http://www.edferrero.com/Content/Cascading_Cell_Validation.xls

Ed Ferrero
www.edferrero.com

 

[Lisa C.]

Thank you, Ed. This information helped, but I am still struggling with my
3rd list which is dependent upon the combination of the 1st and 2nd list.
The 1st list has unique values, and the 2nd list has values that can apply to
multiple values in the 1st list, so the 3rd list has to look at both the 1st
list and 2nd list value. In the example below, the 3rd list has to have a
data validation (Indirect) formula that points to a list for CVS Card Stock
or Kohls Card Stock, based on what is chosen; it can't just point to a Card
Stock list.

Example:
1st List = CVS, 2nd List = Card Stock, 3rd List = CVS Card Stock Displays
1st List = Kohls, 2nd List = Card Stock, 3rd List = Kohls Card Stock Displays

 

[Ed Ferrero]

Hi Lisa,

Thank you, Ed. This information helped, but I am still struggling with my
3rd list which is dependent upon the combination of the 1st and 2nd list.
The 1st list has unique values, and the 2nd list has values that can apply
to
multiple values in the 1st list, so the 3rd list has to look at both the
1st
list and 2nd list value. In the example below, the 3rd list has to have a
data validation (Indirect) formula that points to a list for CVS Card
Stock
or Kohls Card Stock, based on what is chosen; it can't just point to a
Card
Stock list.

Example:
1st List = CVS, 2nd List = Card Stock, 3rd List = CVS Card Stock Displays
1st List = Kohls, 2nd List = Card Stock, 3rd List = Kohls Card Stock
Displays

For the scenario you describe, I would build 3 lists so that each is
dependent on one unique value of the previous;

eg
1st List
CVS
Kohls

2nd List
CVS Card Stock
Kohls Card Stock

3rd List
CVS Card Stock Displays
Kohls Card Stock Displays

Then, using the sample here
http://www.edferrero.com/Content/Cascading_Cell_Validation.xls
1) in the Animals list
- rename Cats to CVS
- rename Dogs to Kohls

2) rename the range names
- Cats to CVS
- Dogs to Kohls

3) in the Cats list (now renamed CVS)
- rename Burmese to CVS_Card_Stock (note the use of underscore characters,
you cannot use spaces in range names)

4) rename the range names
- Burmese to CVS_Card_Stock
- Siamese to Kohls_Card_Stock

5) Finally, change the entries in the 3rd level lists
- CVS Card Stock Displays in CVS_Card_Stock list
etc.

Ed Ferrero
www.edferrero.com

 

[Lisa C.]

Got it! I actually had to do some additional steps to CONCATENATE and
SUBSITUTE to combine the first two list values and eliminate the spaces which
then allowed me to use the INDIRECT formula to the newly calculated value. I
have over 100 lists within my file so my over-simplified example didn't
explain what I was trying to do very well, but your guidance helped me get
there and I really appreciate you taking the time!