Selected Answer
All the lists you need to display must exist either physically or logically. Because of the large number of lists you seem to require the better way should be to prepare them on the fly, filtering them from a master list. But that neerds both VBA and a master list. Since you have neither I shall ignore that possiblity in my answer here.
The key to any solution is having the lists. You have already recognized that and prepared a dedicated sheet for them. That's good and necessary. But you must get away from coding range addresses into formulas unless you want to become a hero (heroes die young). So, this is the first formula I shall give you.
=OFFSET(DROPDOWN!$A$3,0,0,COUNTA(DROPDOWN!$A:$A)-1,2)
Use this formula to specify the range starting at DropDown!A3 and extending to the end of the column, so that you can add or delete items without having to change the formula. Observe that the number of items in the list is determined by COUNTA -1. The one item to be excluded is the column caption. More excluded items in the column requires adjustment. Blanks in the list will cause them to be displayed instead of list members. Also observe that the range has 2 columns (the final 2 determines that). The reason will be explained shortly. Please read on.
You want to use the first column of this named range in the Category drop-down. Observe that I named the range "Specs". The name refers to DROPDOWN!$A$3:$A$224. It's much shorter to use the name, as you see, easier to understand, and dynamic to boot.
=INDEX(Specs,,1)
Normally, the INDEX function specifies a single cell. Here the Row argument is omitted and only the column given, which specifies the entire column, column 1, just as you need it.
Now, normally, you would create a named list using the exact names in the Specs and let your second drop-down (in D5) display the list of that name. You would specify the list with this formula.
=INDIRECT(D4)
I have demonstrated this in D9 of the attached workbook and set up the named range "A2AS" for this purpose. However, this method doesn't work for you because "A2" is a cell reference and can, therefore, not be used as a range name. That's why I added a second column to the Specs range. It has this formula in it.
[B3] ="List"&TEXT(ROW()-2,"000")
Paste to cell B3 and copy down. You get a list of different list names starting from 001 because ROW() returns 3 in row 3 and 3 - 2 = 1.
Now name the list for category "A2" as "List001". Accordingly, cell D5 has this formula for its data validation.
=INDIRECT(VLOOKUP(D4,Specs,2,FALSE))
D4 = "A2" which is looked up in the first column of Specs and the list name returned from the second.
Expand the system to all other lists in your project. Observe that having more than one dynamic list per column is more difficult not only to extract but also to maintain. But you have more than 16000 columns at your disposal. So there is no need to be stingy with their use :-)
