I have a worksheet with an option for a user to choose "0" to "3" in a cell
with a drop down list. Where "0" = N/A, "1" = NI (needs improvement), "2" =
No, and "3" = Yes. This option is located in cell "E16". What I want to do
is create an option for a new drop down list in adjacent cell "G16" which is
based upon the choice made in "E16". A user could choose from several
different appropriate answers for either "3", "2", "1", or "0".

Create your separate lists somewhere. I personally usually have a tab called
Tables, that I will put various tables/lists.
Name your lists. I used names of List0, List1, List2, and List3 for your
example.
Since E16 will have either a blank, 0, 1, 2, or 3 (what they mean is
somewhere else), it is easier to base the new list off the number than what
the numbers mean.

In G16, Data|Validation
Settings: List
Source: =INDIRECT("List"&E16)

Hope this helps.

--
John C


"GerryD_62" wrote:

I have a worksheet with an option for a user to choose "0" to "3" in a cell
with a drop down list. Where "0" = N/A, "1" = NI (needs improvement), "2" =
No, and "3" = Yes. This option is located in cell "E16". What I want to do
is create an option for a new drop down list in adjacent cell "G16" which is
based upon the choice made in "E16". A user could choose from several
different appropriate answers for either "3", "2", "1", or "0".

John C - thanks for your attempt to answer my question. I have the list in a
different worksheet for all 57 questions of my project. Cell "E16" is the
option for the end user "3", "2", "1", or "0". When that number is chosen I
want the cell address in "G16" to provide the appropriate drop down list
based on the number chosen in cell "E16".

I will follow your suggestions as below - I am a little new to this
function, so I am a little lost in the forrest so to speak.

GerryD_62

"John C" wrote:

Create your separate lists somewhere. I personally usually have a tab called
Tables, that I will put various tables/lists.
Name your lists. I used names of List0, List1, List2, and List3 for your
example.
Since E16 will have either a blank, 0, 1, 2, or 3 (what they mean is
somewhere else), it is easier to base the new list off the number than what
the numbers mean.

In G16, Data|Validation
Settings: List
Source: =INDIRECT("List"&E16)

Hope this helps.

--
John C


"GerryD_62" wrote:

I have a worksheet with an option for a user to choose "0" to "3" in a cell
with a drop down list. Where "0" = N/A, "1" = NI (needs improvement), "2" =
No, and "3" = Yes. This option is located in cell "E16". What I want to do
is create an option for a new drop down list in adjacent cell "G16" which is
based upon the choice made in "E16". A user could choose from several
different appropriate answers for either "3", "2", "1", or "0".

The big thing to remember is that in order to reference a list from another
sheet in Data Validation, you must use named ranges. With the indirect
function like I posted, if you follow specific naming regimens, you can
create a varied response to suit your needs. If you need some more help,
please feel free to post back (I'll check it later), and give some more
'specifics'. Such as, how big your other lists are, if they are dependent on
more than just one other list, etc.
--
John C


"GerryD_62" wrote:

John C - thanks for your attempt to answer my question. I have the list in a
different worksheet for all 57 questions of my project. Cell "E16" is the
option for the end user "3", "2", "1", or "0". When that number is chosen I
want the cell address in "G16" to provide the appropriate drop down list
based on the number chosen in cell "E16".

I will follow your suggestions as below - I am a little new to this
function, so I am a little lost in the forrest so to speak.

GerryD_62

"John C" wrote:

Create your separate lists somewhere. I personally usually have a tab called
Tables, that I will put various tables/lists.
Name your lists. I used names of List0, List1, List2, and List3 for your
example.
Since E16 will have either a blank, 0, 1, 2, or 3 (what they mean is
somewhere else), it is easier to base the new list off the number than what
the numbers mean.

In G16, Data|Validation
Settings: List
Source: =INDIRECT("List"&E16)

Hope this helps.

--
John C


"GerryD_62" wrote:

I have a worksheet with an option for a user to choose "0" to "3" in a cell
with a drop down list. Where "0" = N/A, "1" = NI (needs improvement), "2" =
No, and "3" = Yes. This option is located in cell "E16". What I want to do
is create an option for a new drop down list in adjacent cell "G16" which is
based upon the choice made in "E16". A user could choose from several
different appropriate answers for either "3", "2", "1", or "0".

John C,
thanks again for your quick response. The first suggestion did not work for
me. If cell "E16" is a "3" (this means "Yes) then there are 4 different
options for the user to choose in cell "G16"; I have named this "Yes1" (is
the named range). If the answer is "2" (this means "No") and there would be
another 3 different options for the user to choose from in cell "G16", if the
answer is "1" ("NI" for needs improvement) then the user will have 2 options
in "G16" to choose. "0" just remains blank as "NA" or not applicable. This
is the way I have it set for all 57 questions in my program. For now I am
attempting to get #1 working so that I can then apply the same principle to
all 57 questions.

This is an assessment that would be conducted out in the field in which a
user will not have time to type much if anything. This way he/she will
simply pull down from the drop down list what is most appropriate for them.

Did I make this more clear? I hope so. Thank you again sir!

GerryD_62

"John C" wrote:

The big thing to remember is that in order to reference a list from another
sheet in Data Validation, you must use named ranges. With the indirect
function like I posted, if you follow specific naming regimens, you can
create a varied response to suit your needs. If you need some more help,
please feel free to post back (I'll check it later), and give some more
'specifics'. Such as, how big your other lists are, if they are dependent on
more than just one other list, etc.
--
John C


"GerryD_62" wrote:

John C - thanks for your attempt to answer my question. I have the list in a
different worksheet for all 57 questions of my project. Cell "E16" is the
option for the end user "3", "2", "1", or "0". When that number is chosen I
want the cell address in "G16" to provide the appropriate drop down list
based on the number chosen in cell "E16".

I will follow your suggestions as below - I am a little new to this
function, so I am a little lost in the forrest so to speak.

GerryD_62

"John C" wrote:

Create your separate lists somewhere. I personally usually have a tab called
Tables, that I will put various tables/lists.
Name your lists. I used names of List0, List1, List2, and List3 for your
example.
Since E16 will have either a blank, 0, 1, 2, or 3 (what they mean is
somewhere else), it is easier to base the new list off the number than what
the numbers mean.

In G16, Data|Validation
Settings: List
Source: =INDIRECT("List"&E16)

Hope this helps.

--
John C


"GerryD_62" wrote:

I have a worksheet with an option for a user to choose "0" to "3" in a cell
with a drop down list. Where "0" = N/A, "1" = NI (needs improvement), "2" =
No, and "3" = Yes. This option is located in cell "E16". What I want to do
is create an option for a new drop down list in adjacent cell "G16" which is
based upon the choice made in "E16". A user could choose from several
different appropriate answers for either "3", "2", "1", or "0".

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.



--
John C


"GerryD_62" wrote:

John C,
thanks again for your quick response. The first suggestion did not work for
me. If cell "E16" is a "3" (this means "Yes) then there are 4 different
options for the user to choose in cell "G16"; I have named this "Yes1" (is
the named range). If the answer is "2" (this means "No") and there would be
another 3 different options for the user to choose from in cell "G16", if the
answer is "1" ("NI" for needs improvement) then the user will have 2 options
in "G16" to choose. "0" just remains blank as "NA" or not applicable. This
is the way I have it set for all 57 questions in my program. For now I am
attempting to get #1 working so that I can then apply the same principle to
all 57 questions.

This is an assessment that would be conducted out in the field in which a
user will not have time to type much if anything. This way he/she will
simply pull down from the drop down list what is most appropriate for them.

Did I make this more clear? I hope so. Thank you again sir!

GerryD_62

"John C" wrote:

The big thing to remember is that in order to reference a list from another
sheet in Data Validation, you must use named ranges. With the indirect
function like I posted, if you follow specific naming regimens, you can
create a varied response to suit your needs. If you need some more help,
please feel free to post back (I'll check it later), and give some more
'specifics'. Such as, how big your other lists are, if they are dependent on
more than just one other list, etc.
--
John C


"GerryD_62" wrote:

John C - thanks for your attempt to answer my question. I have the list in a
different worksheet for all 57 questions of my project. Cell "E16" is the
option for the end user "3", "2", "1", or "0". When that number is chosen I
want the cell address in "G16" to provide the appropriate drop down list
based on the number chosen in cell "E16".

I will follow your suggestions as below - I am a little new to this
function, so I am a little lost in the forrest so to speak.

GerryD_62

"John C" wrote:

Create your separate lists somewhere. I personally usually have a tab called
Tables, that I will put various tables/lists.
Name your lists. I used names of List0, List1, List2, and List3 for your
example.
Since E16 will have either a blank, 0, 1, 2, or 3 (what they mean is
somewhere else), it is easier to base the new list off the number than what
the numbers mean.

In G16, Data|Validation
Settings: List
Source: =INDIRECT("List"&E16)

Hope this helps.

--
John C


"GerryD_62" wrote:

I have a worksheet with an option for a user to choose "0" to "3" in a cell
with a drop down list. Where "0" = N/A, "1" = NI (needs improvement), "2" =
No, and "3" = Yes. This option is located in cell "E16". What I want to do
is create an option for a new drop down list in adjacent cell "G16" which is
based upon the choice made in "E16". A user could choose from several
different appropriate answers for either "3", "2", "1", or "0".