Okay, here is what I suggest. Again, name secondary lists on a separate tab.
I would use one standard naming convention for all of the lists.
For example (remember, all ranges are on the tab Tables in my example):
My understanding is you have 57 questions, of which they will have a
response of 0,1,2,3, then if they choose 1,2,3 they will need another
dropdown list that will have additional info, and these can be of varying
lengths, etc.
Say question 1 has 4 responses if 1, 3 responses if 2, 7 responses if 3; and
question 2 has 6 responses if 1, 9 responses if 2, 3 responses if 3; etc.
Named ranges on tab, Tables
A2:A5 named List011
B2:B4 named List012
C2:C8 named List013
A11:A16 named List021
B11:B19 named List022
C11:C13 named List023
etc.
Now, on your original tab:
E16 has Data Validation, List, and the choice of 0, 1, 2, or 3. I realize
that the definitions of 0, 1, 2, 3 are NA, NI, No, and Yes, and these
definitions will appear in some other cell. While it is important to appear,
what the 0, 1, 2, and 3 mean are irrelevant to our lists, only 0, 1, 2, and 3
itself is important.
So, assuming E16 is question 1, then
In G16, Data|Validation
Settings: List
Source: =INDIRECT("List01"&E16)
Assuming E19 is question 2, then
In G19, Data|Validation
Settings: List
Source: =INDIRECT("List02"&E19)
The drop down list arrow will appear in G16 and G19 if nothing is chosen in
E16 or E19, but since E16 & E19 are blank, G16 and G19 are looking for lists
List01 and List02 respectively, they don't exist, so nothing will drop down.
If a 0 is chosen, those lists also don't exist (and this is ok, since it
would remain blank), so they cannot choose anything then either. only when
the formulas add a 1, 2, or 3 to the end of List01, List02, etc, will a
dropdown actually be available, as these lists have been defined on your
tables tab.
