validation list help

Hi d0wnt0wn,

There are several ways to do this. Probably the easiest is to use named ranges and a Dynamic Named Range to switch between them. This can get unwieldy if the first list has a lot of items so that the second must be capable of switching between many different lists, in which case a VBA-based solution would be preferred. But for now I'll assume your first list has only two manufacturers, Company A and Company B.

First, give the middle (material category) lists used for the two companies names (range names). To do this, select each list (range), then enter a name in the Name box above cell A1. The name must not contain spaces. For this example, I assume you name the two lists "MatCatA" and "MatCatB". Now assume the manufacturer selection dropdown is in cell B4, and you want the dropdown list in C4 to switch from validating using list MatCatA and MatCatB depending on the company selected in B4.

Second, define a Dynamic Range that is calculated according to the value in B4. To do this go to Insert > Define > Define. Under Names In Workbook, enter a name like "MatCat", then in the Refers To box enter the formula

=IF(B4="Company A",MatCatA,MatCatB)

Click Add and OK.

Third, for the Validation list in cell C4 use "=MatCat" as the validation list Source.

That should do it.

I hope you can see from this how you can extend this to handle more companies, and to set up your material accessories lists the same way.

But again, if the number of companies is large you should switch to a VBA implementation. Let me know if you need this. It is not difficult, just a bit different, and can handle a virtually unlimited number of companies and levels of nesting of the lists.

Damon

thanks damon ill try it in the morning.... and no the company list is very small so i wont have to worry about all that... thanks again