multiple lookup lists

Hi, 

I am not sure how to complete the list column lookups in this site. Probably need to redo the lists. 

Or .... the solution is probably to use a SharePoint Form?

Here we go....

Here's the list for the end user, Drawing List:

Dwg Number is a Title column, value entered by the user

Category Code and Name are 2 columns resulting from user selected lookup here are the first few columns in the Category List (there are actually 70):

It's the final 2 columns I am struggling to figure out.

2 more lists

Drawing Type List (example rows):

and the relationship list, providing available drawing types per Category using only the Code values in columns named Dwg-Type-By-Category (example inly):

I am hoping that when a user creates a new Drawing in the Drawing List, they provide the value for the drawing number, then they select the applicable category. The lookup provides category name and code. Then the hard part that I cannot figure out, hence this post. Need a lookup that will filter only the applicable dwg types using the category code AND provide a list of drawing type NAMES. That's lookup in the relationship list and lookup in the dwg type list to provide the dwg type names to the user.

In the example drawing list, the lookup listed ... General Plan (C) and Key Plan (s) for user selection following user selection of 02 (Survey and Cadastral) category.  

I hope this makes sense.

Thanks for reading and thx for your patience.

Kevin.