Thread: Dynamic data validation using multiple lists

I hope someone can help me (or point me in the direction of an already solved query) before I plunge myself through the nearest window and into the apparently bottomless fountain in the quadrangle.

I am attempting to create a form in excel with dynamic data validation fields - the thing that is killing me is that I want to create multiple dependencies (ie, if you select something from a drop-down list in the first field, that then gives you a specific range in the second). What I can't seem to work out is the formula for making this work.

The format I'm following: if I select, for example, ACAT in the first list, I want that to then bring up the ACAT-dependent range in the drop-down list in the next cell. If I then select ACHE in the first list, I want that to bring up the ACHE-dependent range in the drop-down, and so on. I have named my ranges and column headings, it's just getting the formula to agree that I fail at.

Is this impossible? In an ideal world, I'd like to have one category list and two or three dependents, but seeing as I can't even get one to work...