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".

John C,
your answer definitely makes sense to me - I really like the "List011...."
idea. One question though, as "List011" will equal to "1" on the "E16" drop
down list, would I also need to create an "INDIRECT" for "List012" and
"List013" as well? "List" is what I understand I begin with; then "01" would
mean question #1, and "1" would correspond in this case to "NI" in which
there are two drop down options in this section, four in "2", and four in "3".

Hope this is not too confusing and I will certainly give you a very
favorable rating when I finally "hunker" this down.

Sincerely,
GerryD_62

"John C" wrote:

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".

No, assuming you have the named ranges as
List011, List012, List013, List021, List022, List023, etc. as i described
below
and assuming your data validation in column G is as i described below
G16: =INDIRECT("List01"&E16)
G19: =INDIRECT("List02"&E19)
etc.

Let's just look at 1 of the statements (G19), as the others will behave
accordingly.
E19 has 5 possible values, blank, 0, 1, 2, and 3. Here is how G19 will
handle this statement for each value.
E19: BLANK G19: =INDIRECT("List02") ... since List02 & blank is
List02
E19: 0 G19: =INDIRECT("List020") ... since List02 & 0 is
List020
E19: 1 G19: =INDIRECT("List021") ... since List02 & 1 is
List021
E19: 2 G19: =INDIRECT("List022") ... since List02 & 2 is
List022
E19: 3 G19: =INDIRECT("List023") ... since List02 & 3 is
List023
so the indirect formula will look for 1 of 5 lists, List02, List020,
List021, List022, List023, and since List02 and List020 do not exist (were
never defined by you), the pull down arrow, while visible when in the cell,
will not yield any choices, and you won't be able to type anything in it (you
can type, but as soon as you try to enter, it will error). The other 3
possible outcomes of List021, List022, and List023 are valid lists, and the
INDIRECT statement will access the appropriate list.
--
John C


"GerryD_62" wrote:

John C,
your answer definitely makes sense to me - I really like the "List011...."
idea. One question though, as "List011" will equal to "1" on the "E16" drop
down list, would I also need to create an "INDIRECT" for "List012" and
"List013" as well? "List" is what I understand I begin with; then "01" would
mean question #1, and "1" would correspond in this case to "NI" in which
there are two drop down options in this section, four in "2", and four in "3".

Hope this is not too confusing and I will certainly give you a very
favorable rating when I finally "hunker" this down.

Sincerely,
GerryD_62

"John C" wrote:

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".

John C,
thanks again for your assistance. I think I am getting it but will need to
tinker some more - you are very knowledgeable and I appreciate your
assistance! I will post again on this as I experiment with your latest
suggestion.

Sincerely,
GerryD_62


"John C" wrote:

No, assuming you have the named ranges as
List011, List012, List013, List021, List022, List023, etc. as i described
below
and assuming your data validation in column G is as i described below
G16: =INDIRECT("List01"&E16)
G19: =INDIRECT("List02"&E19)
etc.

Let's just look at 1 of the statements (G19), as the others will behave
accordingly.
E19 has 5 possible values, blank, 0, 1, 2, and 3. Here is how G19 will
handle this statement for each value.
E19: BLANK G19: =INDIRECT("List02") ... since List02 & blank is
List02
E19: 0 G19: =INDIRECT("List020") ... since List02 & 0 is
List020
E19: 1 G19: =INDIRECT("List021") ... since List02 & 1 is
List021
E19: 2 G19: =INDIRECT("List022") ... since List02 & 2 is
List022
E19: 3 G19: =INDIRECT("List023") ... since List02 & 3 is
List023
so the indirect formula will look for 1 of 5 lists, List02, List020,
List021, List022, List023, and since List02 and List020 do not exist (were
never defined by you), the pull down arrow, while visible when in the cell,
will not yield any choices, and you won't be able to type anything in it (you
can type, but as soon as you try to enter, it will error). The other 3
possible outcomes of List021, List022, and List023 are valid lists, and the
INDIRECT statement will access the appropriate list.
--
John C


"GerryD_62" wrote:

John C,
your answer definitely makes sense to me - I really like the "List011...."
idea. One question though, as "List011" will equal to "1" on the "E16" drop
down list, would I also need to create an "INDIRECT" for "List012" and
"List013" as well? "List" is what I understand I begin with; then "01" would
mean question #1, and "1" would correspond in this case to "NI" in which
there are two drop down options in this section, four in "2", and four in "3".

Hope this is not too confusing and I will certainly give you a very
favorable rating when I finally "hunker" this down.

Sincerely,
GerryD_62

"John C" wrote:

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".

John C,
I appreciate your patience. I do not need to insert a code for "blank" as
"0" is blank it will simply remain that way. Where "1" is "NI", "2" is "No",
and "3" is "Yes". I have right now for cell address "E16"; List013 (for
Yes), List012 (for No), and List011 (For NI).

It worked!!!! Now, I will have to save these notes - please come by here
tomorrow in case I forget??? (Ughhh!). A lot of work on this matter!!! Now
it is time for you to get a GREAT rating sir. Couldn't get it to work at
first...but, then re-read and voila!

GerryD_62

"John C" wrote:

No, assuming you have the named ranges as
List011, List012, List013, List021, List022, List023, etc. as i described
below
and assuming your data validation in column G is as i described below
G16: =INDIRECT("List01"&E16)
G19: =INDIRECT("List02"&E19)
etc.

Let's just look at 1 of the statements (G19), as the others will behave
accordingly.
E19 has 5 possible values, blank, 0, 1, 2, and 3. Here is how G19 will
handle this statement for each value.
E19: BLANK G19: =INDIRECT("List02") ... since List02 & blank is
List02
E19: 0 G19: =INDIRECT("List020") ... since List02 & 0 is
List020
E19: 1 G19: =INDIRECT("List021") ... since List02 & 1 is
List021
E19: 2 G19: =INDIRECT("List022") ... since List02 & 2 is
List022
E19: 3 G19: =INDIRECT("List023") ... since List02 & 3 is
List023
so the indirect formula will look for 1 of 5 lists, List02, List020,
List021, List022, List023, and since List02 and List020 do not exist (were
never defined by you), the pull down arrow, while visible when in the cell,
will not yield any choices, and you won't be able to type anything in it (you
can type, but as soon as you try to enter, it will error). The other 3
possible outcomes of List021, List022, and List023 are valid lists, and the
INDIRECT statement will access the appropriate list.
--
John C


"GerryD_62" wrote:

John C,
your answer definitely makes sense to me - I really like the "List011...."
idea. One question though, as "List011" will equal to "1" on the "E16" drop
down list, would I also need to create an "INDIRECT" for "List012" and
"List013" as well? "List" is what I understand I begin with; then "01" would
mean question #1, and "1" would correspond in this case to "NI" in which
there are two drop down options in this section, four in "2", and four in "3".

Hope this is not too confusing and I will certainly give you a very
favorable rating when I finally "hunker" this down.

Sincerely,
GerryD_62

"John C" wrote:

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".

Glad I could help, and thanks for the feedback. I and many others (many of
whom are much more knowledgeable than I), look through these forms nearly
daily.
:)
--
John C


"GerryD_62" wrote:

John C,
I appreciate your patience. I do not need to insert a code for "blank" as
"0" is blank it will simply remain that way. Where "1" is "NI", "2" is "No",
and "3" is "Yes". I have right now for cell address "E16"; List013 (for
Yes), List012 (for No), and List011 (For NI).

It worked!!!! Now, I will have to save these notes - please come by here
tomorrow in case I forget??? (Ughhh!). A lot of work on this matter!!! Now
it is time for you to get a GREAT rating sir. Couldn't get it to work at
first...but, then re-read and voila!

GerryD_62

"John C" wrote:

No, assuming you have the named ranges as
List011, List012, List013, List021, List022, List023, etc. as i described
below
and assuming your data validation in column G is as i described below
G16: =INDIRECT("List01"&E16)
G19: =INDIRECT("List02"&E19)
etc.

Let's just look at 1 of the statements (G19), as the others will behave
accordingly.
E19 has 5 possible values, blank, 0, 1, 2, and 3. Here is how G19 will
handle this statement for each value.
E19: BLANK G19: =INDIRECT("List02") ... since List02 & blank is
List02
E19: 0 G19: =INDIRECT("List020") ... since List02 & 0 is
List020
E19: 1 G19: =INDIRECT("List021") ... since List02 & 1 is
List021
E19: 2 G19: =INDIRECT("List022") ... since List02 & 2 is
List022
E19: 3 G19: =INDIRECT("List023") ... since List02 & 3 is
List023
so the indirect formula will look for 1 of 5 lists, List02, List020,
List021, List022, List023, and since List02 and List020 do not exist (were
never defined by you), the pull down arrow, while visible when in the cell,
will not yield any choices, and you won't be able to type anything in it (you
can type, but as soon as you try to enter, it will error). The other 3
possible outcomes of List021, List022, and List023 are valid lists, and the
INDIRECT statement will access the appropriate list.
--
John C


"GerryD_62" wrote:

John C,
your answer definitely makes sense to me - I really like the "List011...."
idea. One question though, as "List011" will equal to "1" on the "E16" drop
down list, would I also need to create an "INDIRECT" for "List012" and
"List013" as well? "List" is what I understand I begin with; then "01" would
mean question #1, and "1" would correspond in this case to "NI" in which
there are two drop down options in this section, four in "2", and four in "3".

Hope this is not too confusing and I will certainly give you a very
favorable rating when I finally "hunker" this down.

Sincerely,
GerryD_62

"John C" wrote:

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".