I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe if
i picked for example warranty work that another list would drop down and let
me pick manufacturing or vendor then another list would drop down and i could
pick what kinda manufact. or vendor warranty it was. this may not be possible
though it would be a great thing to implement in the future i need a way that
is user friendly to detail what work was done. any ideas?

This kind lady will help you.
http://www.contextures.com/tiptech.html


--
Don Guillett
SalesAid Software

"Sburlingham" wrote in message
...
I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe
if
i picked for example warranty work that another list would drop down and
let
me pick manufacturing or vendor then another list would drop down and i
could
pick what kinda manufact. or vendor warranty it was. this may not be
possible
though it would be a great thing to implement in the future i need a way
that
is user friendly to detail what work was done. any ideas?

You can set up two separate drop down lists that are linked, if you use the
actual controls and not just the within-cell validation. Now that I think
about it, you could probably use the in-cell validation too, although I'd
have to think about the details if you had a bunch of rows where you wanted
to do this selection.

If you set a cell to equal your first combobox value (or first data
validation cell, etc.), then use that as a source for vlookup which could
return a column letter for your target sub-list. Maybe put this value in
your third column, so that each of your sub-list selection cells would be
able to use it's own pointer to the appropriate sublist. Then set your
second combobox or data validation cell to a named range that picks your
sub-list using the "indirect" worksheet function.

That's just off the top of my head, and the details really will depend on
whether you are using data validation or comboboxes (and maybe whether you
are using comboboxes on the worksheet itself, or on a userform)



"Sburlingham" wrote in message
...
I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe
if
i picked for example warranty work that another list would drop down and
let
me pick manufacturing or vendor then another list would drop down and i
could
pick what kinda manufact. or vendor warranty it was. this may not be
possible
though it would be a great thing to implement in the future i need a way
that
is user friendly to detail what work was done. any ideas?

ok maybe i should tell you what exactly it is for... i'm creating a time
sheet for field opperators so they can tell us what they did, for how long,
and on what. ie sam worked 3 hours on warranty(selection from first list) i
want it to prompt him with was it manufacturing or vendor (selection from
second list) then prompt for type of work done (selection from third list)

i used data validation and am not familiar(i don't think) with controls or
comboboxes meaning if i have used them it's been a really long time since. i
asked one of the IT guys and he said access would allow me to do that but
they would like it in excel.

and i thought i knew this program!!!!!
"KR" wrote:

You can set up two separate drop down lists that are linked, if you use the
actual controls and not just the within-cell validation. Now that I think
about it, you could probably use the in-cell validation too, although I'd
have to think about the details if you had a bunch of rows where you wanted
to do this selection.

If you set a cell to equal your first combobox value (or first data
validation cell, etc.), then use that as a source for vlookup which could
return a column letter for your target sub-list. Maybe put this value in
your third column, so that each of your sub-list selection cells would be
able to use it's own pointer to the appropriate sublist. Then set your
second combobox or data validation cell to a named range that picks your
sub-list using the "indirect" worksheet function.

That's just off the top of my head, and the details really will depend on
whether you are using data validation or comboboxes (and maybe whether you
are using comboboxes on the worksheet itself, or on a userform)



"Sburlingham" wrote in message
...
I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe
if
i picked for example warranty work that another list would drop down and
let
me pick manufacturing or vendor then another list would drop down and i
could
pick what kinda manufact. or vendor warranty it was. this may not be
possible
though it would be a great thing to implement in the future i need a way
that
is user friendly to detail what work was done. any ideas?

ok so i found combobox in the control toolbox however i don't believe this is
what i'm looking for. im beginning to think that i'm going to have to give a
reference list and have them type what job they did. the other issue is that
i can't have this sheet(s) be too long i'm looking for the easiest way using
the least amount of space possible. i'm sorry if im bombarding you and thank
you for your all your help!!!!

"KR" wrote:

You can set up two separate drop down lists that are linked, if you use the
actual controls and not just the within-cell validation. Now that I think
about it, you could probably use the in-cell validation too, although I'd
have to think about the details if you had a bunch of rows where you wanted
to do this selection.

If you set a cell to equal your first combobox value (or first data
validation cell, etc.), then use that as a source for vlookup which could
return a column letter for your target sub-list. Maybe put this value in
your third column, so that each of your sub-list selection cells would be
able to use it's own pointer to the appropriate sublist. Then set your
second combobox or data validation cell to a named range that picks your
sub-list using the "indirect" worksheet function.

That's just off the top of my head, and the details really will depend on
whether you are using data validation or comboboxes (and maybe whether you
are using comboboxes on the worksheet itself, or on a userform)



"Sburlingham" wrote in message
...
I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe
if
i picked for example warranty work that another list would drop down and
let
me pick manufacturing or vendor then another list would drop down and i
could
pick what kinda manufact. or vendor warranty it was. this may not be
possible
though it would be a great thing to implement in the future i need a way
that
is user friendly to detail what work was done. any ideas?

Try this, from which you should be able to build what you want.

On sheet1, set up the source lists for validation:
A B C D E F
Sow A 1 Get Seed Fill tank Get rake
Mow B 2 Fill hopper Start mower rake piles
Rake C 3 Spread Seed Mow yard bag piles


A is your main list (job) and D,E,F are your potential sub-lists for each
job. C is just for hours spent, which would be just a normal data validation
list with no dependencies.

Name your ranges for A, D, E, F. You have to use something that isn't a
keyword in Excel (in any language) but you do want to include the reference
from B, so for example A would be "jobs", D would be "A_range", E would be
"B_range", and F would be "C_range". Note that these sub-ranges each have to
have the exact same name except for the letter reference.

Then set your first data validation cell to =jobs. On the cell just to the
right of that one, put in a vlookup, looking for the source data validation
value in your range of A1:B3 on the source sheet described above. Make sure
your last parameter requires an exact match [e.g.
=VLOOKUP(A1,Sheet1!A1:B3,2,FALSE)]. For the sake of this example, let's say
that you have your main JOBS data validation in A1, and your vlookup in B1.
Then in your next data validation cell (the sub-list one) make it
=concatenate(B1 & "_range") and it will pull the appropriate sublist each
time the JOBS cell is changed.

Warning: if you change the main jobs selection, it will not automatically
blank out the previous selection in the sub-list, even though the sub-list
options have changed. There may be a way to do so, but it isn't coming to me
at the moment.

HTH,
Keith



"Sburlingham" wrote in message
...
ok so i found combobox in the control toolbox however i don't believe this
is
what i'm looking for. im beginning to think that i'm going to have to give
a
reference list and have them type what job they did. the other issue is
that
i can't have this sheet(s) be too long i'm looking for the easiest way
using
the least amount of space possible. i'm sorry if im bombarding you and
thank
you for your all your help!!!!

"KR" wrote:

You can set up two separate drop down lists that are linked, if you use
the
actual controls and not just the within-cell validation. Now that I
think
about it, you could probably use the in-cell validation too, although
I'd
have to think about the details if you had a bunch of rows where you
wanted
to do this selection.

If you set a cell to equal your first combobox value (or first data
validation cell, etc.), then use that as a source for vlookup which
could
return a column letter for your target sub-list. Maybe put this value in
your third column, so that each of your sub-list selection cells would
be
able to use it's own pointer to the appropriate sublist. Then set your
second combobox or data validation cell to a named range that picks your
sub-list using the "indirect" worksheet function.

That's just off the top of my head, and the details really will depend
on
whether you are using data validation or comboboxes (and maybe whether
you
are using comboboxes on the worksheet itself, or on a userform)



"Sburlingham" wrote in message
...
I have a problem, i created a drop down list of jobs that could have
been
preformed i need to create another list for each of those jobs to be
more
specific with what exactly was preformed i had this great idea that
maybe
if
i picked for example warranty work that another list would drop down
and
let
me pick manufacturing or vendor then another list would drop down and
i
could
pick what kinda manufact. or vendor warranty it was. this may not be
possible
though it would be a great thing to implement in the future i need a
way
that
is user friendly to detail what work was done. any ideas?

ok thank you that helped alot i really appreciate it.

"KR" wrote:

Try this, from which you should be able to build what you want.

On sheet1, set up the source lists for validation:
A B C D E F
Sow A 1 Get Seed Fill tank Get rake
Mow B 2 Fill hopper Start mower rake piles
Rake C 3 Spread Seed Mow yard bag piles


A is your main list (job) and D,E,F are your potential sub-lists for each
job. C is just for hours spent, which would be just a normal data validation
list with no dependencies.

Name your ranges for A, D, E, F. You have to use something that isn't a
keyword in Excel (in any language) but you do want to include the reference
from B, so for example A would be "jobs", D would be "A_range", E would be
"B_range", and F would be "C_range". Note that these sub-ranges each have to
have the exact same name except for the letter reference.

Then set your first data validation cell to =jobs. On the cell just to the
right of that one, put in a vlookup, looking for the source data validation
value in your range of A1:B3 on the source sheet described above. Make sure
your last parameter requires an exact match [e.g.
=VLOOKUP(A1,Sheet1!A1:B3,2,FALSE)]. For the sake of this example, let's say
that you have your main JOBS data validation in A1, and your vlookup in B1.
Then in your next data validation cell (the sub-list one) make it
=concatenate(B1 & "_range") and it will pull the appropriate sublist each
time the JOBS cell is changed.

Warning: if you change the main jobs selection, it will not automatically
blank out the previous selection in the sub-list, even though the sub-list
options have changed. There may be a way to do so, but it isn't coming to me
at the moment.

HTH,
Keith



"Sburlingham" wrote in message
...
ok so i found combobox in the control toolbox however i don't believe this
is
what i'm looking for. im beginning to think that i'm going to have to give
a
reference list and have them type what job they did. the other issue is
that
i can't have this sheet(s) be too long i'm looking for the easiest way
using
the least amount of space possible. i'm sorry if im bombarding you and
thank
you for your all your help!!!!

"KR" wrote:

You can set up two separate drop down lists that are linked, if you use
the
actual controls and not just the within-cell validation. Now that I
think
about it, you could probably use the in-cell validation too, although
I'd
have to think about the details if you had a bunch of rows where you
wanted
to do this selection.

If you set a cell to equal your first combobox value (or first data
validation cell, etc.), then use that as a source for vlookup which
could
return a column letter for your target sub-list. Maybe put this value in
your third column, so that each of your sub-list selection cells would
be
able to use it's own pointer to the appropriate sublist. Then set your
second combobox or data validation cell to a named range that picks your
sub-list using the "indirect" worksheet function.

That's just off the top of my head, and the details really will depend
on
whether you are using data validation or comboboxes (and maybe whether
you
are using comboboxes on the worksheet itself, or on a userform)



"Sburlingham" wrote in message
...
I have a problem, i created a drop down list of jobs that could have
been
preformed i need to create another list for each of those jobs to be
more
specific with what exactly was preformed i had this great idea that
maybe
if
i picked for example warranty work that another list would drop down
and
let
me pick manufacturing or vendor then another list would drop down and
i
could
pick what kinda manufact. or vendor warranty it was. this may not be
possible
though it would be a great thing to implement in the future i need a
way
that
is user friendly to detail what work was done. any ideas?

I am not sure if this is how I do this so if I am wrong I want to appologize
in advance.
I am trying to find a reference letter template from a vendor. This is what
it's about! I am currently working as a contractor and the company I am
contracted to told me to apply to them as a fulltime employee. I have spoke
to a few of my vendors that I have worked with for a year now and they said
that if I type up a template they will finish it. Do you have any
suggestions on how to write a reference letter like this?

"Sburlingham" wrote:

ok thank you that helped alot i really appreciate it.

"KR" wrote:

Try this, from which you should be able to build what you want.

On sheet1, set up the source lists for validation:
A B C D E F
Sow A 1 Get Seed Fill tank Get rake
Mow B 2 Fill hopper Start mower rake piles
Rake C 3 Spread Seed Mow yard bag piles


A is your main list (job) and D,E,F are your potential sub-lists for each
job. C is just for hours spent, which would be just a normal data validation
list with no dependencies.

Name your ranges for A, D, E, F. You have to use something that isn't a
keyword in Excel (in any language) but you do want to include the reference
from B, so for example A would be "jobs", D would be "A_range", E would be
"B_range", and F would be "C_range". Note that these sub-ranges each have to
have the exact same name except for the letter reference.

Then set your first data validation cell to =jobs. On the cell just to the
right of that one, put in a vlookup, looking for the source data validation
value in your range of A1:B3 on the source sheet described above. Make sure
your last parameter requires an exact match [e.g.
=VLOOKUP(A1,Sheet1!A1:B3,2,FALSE)]. For the sake of this example, let's say
that you have your main JOBS data validation in A1, and your vlookup in B1.
Then in your next data validation cell (the sub-list one) make it
=concatenate(B1 & "_range") and it will pull the appropriate sublist each
time the JOBS cell is changed.

Warning: if you change the main jobs selection, it will not automatically
blank out the previous selection in the sub-list, even though the sub-list
options have changed. There may be a way to do so, but it isn't coming to me
at the moment.

HTH,
Keith



"Sburlingham" wrote in message
...
ok so i found combobox in the control toolbox however i don't believe this
is
what i'm looking for. im beginning to think that i'm going to have to give
a
reference list and have them type what job they did. the other issue is
that
i can't have this sheet(s) be too long i'm looking for the easiest way
using
the least amount of space possible. i'm sorry if im bombarding you and
thank
you for your all your help!!!!

"KR" wrote:

You can set up two separate drop down lists that are linked, if you use
the
actual controls and not just the within-cell validation. Now that I
think
about it, you could probably use the in-cell validation too, although
I'd
have to think about the details if you had a bunch of rows where you
wanted
to do this selection.

If you set a cell to equal your first combobox value (or first data
validation cell, etc.), then use that as a source for vlookup which
could
return a column letter for your target sub-list. Maybe put this value in
your third column, so that each of your sub-list selection cells would
be
able to use it's own pointer to the appropriate sublist. Then set your
second combobox or data validation cell to a named range that picks your
sub-list using the "indirect" worksheet function.

That's just off the top of my head, and the details really will depend
on
whether you are using data validation or comboboxes (and maybe whether
you
are using comboboxes on the worksheet itself, or on a userform)



"Sburlingham" wrote in message
...
I have a problem, i created a drop down list of jobs that could have
been
preformed i need to create another list for each of those jobs to be
more
specific with what exactly was preformed i had this great idea that
maybe
if
i picked for example warranty work that another list would drop down
and
let
me pick manufacturing or vendor then another list would drop down and
i
could
pick what kinda manufact. or vendor warranty it was. this may not be
possible
though it would be a great thing to implement in the future i need a
way
that
is user friendly to detail what work was done. any ideas?

This is a news group for discussing Excel spreadsheet. You don't type
reference letters in Excel, might I suggest you use Word to create your
letter.

LIronmonger wrote:

I am not sure if this is how I do this so if I am wrong I want to appologize
in advance.
I am trying to find a reference letter template from a vendor. This is what
it's about! I am currently working as a contractor and the company I am
contracted to told me to apply to them as a fulltime employee. I have spoke
to a few of my vendors that I have worked with for a year now and they said
that if I type up a template they will finish it. Do you have any
suggestions on how to write a reference letter like this?

"Sburlingham" wrote:


ok thank you that helped alot i really appreciate it.

"KR" wrote:


Try this, from which you should be able to build what you want.

On sheet1, set up the source lists for validation:
A B C D E F
Sow A 1 Get Seed Fill tank Get rake
Mow B 2 Fill hopper Start mower rake piles
Rake C 3 Spread Seed Mow yard bag piles


A is your main list (job) and D,E,F are your potential sub-lists for each
job. C is just for hours spent, which would be just a normal data validation
list with no dependencies.

Name your ranges for A, D, E, F. You have to use something that isn't a
keyword in Excel (in any language) but you do want to include the reference
from B, so for example A would be "jobs", D would be "A_range", E would be
"B_range", and F would be "C_range". Note that these sub-ranges each have to
have the exact same name except for the letter reference.

Then set your first data validation cell to =jobs. On the cell just to the
right of that one, put in a vlookup, looking for the source data validation
value in your range of A1:B3 on the source sheet described above. Make sure
your last parameter requires an exact match [e.g.
=VLOOKUP(A1,Sheet1!A1:B3,2,FALSE)]. For the sake of this example, let's say
that you have your main JOBS data validation in A1, and your vlookup in B1.
Then in your next data validation cell (the sub-list one) make it
=concatenate(B1 & "_range") and it will pull the appropriate sublist each
time the JOBS cell is changed.

Warning: if you change the main jobs selection, it will not automatically
blank out the previous selection in the sub-list, even though the sub-list
options have changed. There may be a way to do so, but it isn't coming to me
at the moment.

HTH,
Keith



"Sburlingham" wrote in message
...

ok so i found combobox in the control toolbox however i don't believe this

is

what i'm looking for. im beginning to think that i'm going to have to give

a

reference list and have them type what job they did. the other issue is

that

i can't have this sheet(s) be too long i'm looking for the easiest way

using

the least amount of space possible. i'm sorry if im bombarding you and

thank

you for your all your help!!!!

"KR" wrote:


You can set up two separate drop down lists that are linked, if you use

the

actual controls and not just the within-cell validation. Now that I

think

about it, you could probably use the in-cell validation too, although

I'd

have to think about the details if you had a bunch of rows where you

wanted

to do this selection.

If you set a cell to equal your first combobox value (or first data
validation cell, etc.), then use that as a source for vlookup which

could

return a column letter for your target sub-list. Maybe put this value in
your third column, so that each of your sub-list selection cells would

be

able to use it's own pointer to the appropriate sublist. Then set your
second combobox or data validation cell to a named range that picks your
sub-list using the "indirect" worksheet function.

That's just off the top of my head, and the details really will depend

on

whether you are using data validation or comboboxes (and maybe whether

you

are using comboboxes on the worksheet itself, or on a userform)



"Sburlingham" wrote in message
...

I have a problem, i created a drop down list of jobs that could have

been

preformed i need to create another list for each of those jobs to be

more

specific with what exactly was preformed i had this great idea that

maybe

if

i picked for example warranty work that another list would drop down

and

let

me pick manufacturing or vendor then another list would drop down and

i

could

pick what kinda manufact. or vendor warranty it was. this may not be

possible

though it would be a great thing to implement in the future i need a

way

that

is user friendly to detail what work was done. any ideas?

I am attempting to have the above work on my spreadsheet, and I have been
successfull up to the following point from the above post -- "Then in your
next data validation cell (the sub-list one) make it =concatenate(B1 &
"_range") and it will pull the appropriate sublist each time the JOBS cell is
changed. "

I can concatenate and receive the correct data name used for the range of
data I would like to show up in this second list. The problem is getting the
list to appear, it doesn't. How do I create a Data Validate list that uses
the results from the concantenate function?

I have attempted to use the Data Validation window in the following ways to
have the sub-list appear.
1. select "List" and attempt to put in the concatenate function in the
"Source" entry box, but it will not allow it.
2. select "Custom" and put the concantenate function in the function entry
box - doesnt work.
3. select "List" and attempt to referance the cell where the concantenate
function reside - doesnt work
4. Put the Concantenate function in the cell where I want the list to
appear, and then try to make the cell a list cell - doesnt work.

Please clarify the above post by explaining how to have the second list
appear from the results of the concantenate function.

As further background, my named ranges of data are on a seperate worksheet
in this same workbook. I have paid attention to capitalization of the named
ranges and the output of the concantenate function.
"KR" wrote:

Try this, from which you should be able to build what you want.

On sheet1, set up the source lists for validation:
A B C D E F
Sow A 1 Get Seed Fill tank Get rake
Mow B 2 Fill hopper Start mower rake piles
Rake C 3 Spread Seed Mow yard bag piles


A is your main list (job) and D,E,F are your potential sub-lists for each
job. C is just for hours spent, which would be just a normal data validation
list with no dependencies.

Name your ranges for A, D, E, F. You have to use something that isn't a
keyword in Excel (in any language) but you do want to include the reference
from B, so for example A would be "jobs", D would be "A_range", E would be
"B_range", and F would be "C_range". Note that these sub-ranges each have to
have the exact same name except for the letter reference.

Then set your first data validation cell to =jobs. On the cell just to the
right of that one, put in a vlookup, looking for the source data validation
value in your range of A1:B3 on the source sheet described above. Make sure
your last parameter requires an exact match [e.g.
=VLOOKUP(A1,Sheet1!A1:B3,2,FALSE)]. For the sake of this example, let's say
that you have your main JOBS data validation in A1, and your vlookup in B1.
Then in your next data validation cell (the sub-list one) make it
=concatenate(B1 & "_range") and it will pull the appropriate sublist each
time the JOBS cell is changed.

Warning: if you change the main jobs selection, it will not automatically
blank out the previous selection in the sub-list, even though the sub-list
options have changed. There may be a way to do so, but it isn't coming to me
at the moment.

HTH,
Keith



"Sburlingham" wrote in message
...
ok so i found combobox in the control toolbox however i don't believe this
is
what i'm looking for. im beginning to think that i'm going to have to give
a
reference list and have them type what job they did. the other issue is
that
i can't have this sheet(s) be too long i'm looking for the easiest way
using
the least amount of space possible. i'm sorry if im bombarding you and
thank
you for your all your help!!!!

"KR" wrote:

You can set up two separate drop down lists that are linked, if you use
the
actual controls and not just the within-cell validation. Now that I
think
about it, you could probably use the in-cell validation too, although
I'd
have to think about the details if you had a bunch of rows where you
wanted
to do this selection.

If you set a cell to equal your first combobox value (or first data
validation cell, etc.), then use that as a source for vlookup which
could
return a column letter for your target sub-list. Maybe put this value in
your third column, so that each of your sub-list selection cells would
be
able to use it's own pointer to the appropriate sublist. Then set your
second combobox or data validation cell to a named range that picks your
sub-list using the "indirect" worksheet function.

That's just off the top of my head, and the details really will depend
on
whether you are using data validation or comboboxes (and maybe whether
you
are using comboboxes on the worksheet itself, or on a userform)



"Sburlingham" wrote in message
...
I have a problem, i created a drop down list of jobs that could have
been
preformed i need to create another list for each of those jobs to be
more
specific with what exactly was preformed i had this great idea that
maybe
if
i picked for example warranty work that another list would drop down
and
let
me pick manufacturing or vendor then another list would drop down and
i
could
pick what kinda manufact. or vendor warranty it was. this may not be
possible
though it would be a great thing to implement in the future i need a
way
that
is user friendly to detail what work was done. any ideas?

Hi Dev,


See Debra Dalgleish at:

Data Validation -- Dependent Dropdowns from a Sorted List
http://www.contextures.com/xlDataVal13.html

and

Data Validation -- Create Dependent Lists
http://www.contextures.com/xlDataVal02.html

See also Bob Phillips Dependent Dropdowns
page at his xlDynamic site:

Dynamic Dependent Dropdowns
http://www.xldynamic.com/source/xld.Dropdowns.html



---
Regards.
Norman


"DevKMDonnan" wrote in message
...
I am attempting to have the above work on my spreadsheet, and I have been
successfull up to the following point from the above post -- "Then in your
next data validation cell (the sub-list one) make it =concatenate(B1 &
"_range") and it will pull the appropriate sublist each time the JOBS cell
is
changed. "

I can concatenate and receive the correct data name used for the range of
data I would like to show up in this second list. The problem is getting
the
list to appear, it doesn't. How do I create a Data Validate list that uses
the results from the concantenate function?

I have attempted to use the Data Validation window in the following ways
to
have the sub-list appear.
1. select "List" and attempt to put in the concatenate function in the
"Source" entry box, but it will not allow it.
2. select "Custom" and put the concantenate function in the function entry
box - doesnt work.
3. select "List" and attempt to referance the cell where the concantenate
function reside - doesnt work
4. Put the Concantenate function in the cell where I want the list to
appear, and then try to make the cell a list cell - doesnt work.

Please clarify the above post by explaining how to have the second list
appear from the results of the concantenate function.

As further background, my named ranges of data are on a seperate worksheet
in this same workbook. I have paid attention to capitalization of the
named
ranges and the output of the concantenate function.
"KR" wrote:

Try this, from which you should be able to build what you want.

On sheet1, set up the source lists for validation:
A B C D E F
Sow A 1 Get Seed Fill tank Get rake
Mow B 2 Fill hopper Start mower rake piles
Rake C 3 Spread Seed Mow yard bag piles


A is your main list (job) and D,E,F are your potential sub-lists for each
job. C is just for hours spent, which would be just a normal data
validation
list with no dependencies.

Name your ranges for A, D, E, F. You have to use something that isn't a
keyword in Excel (in any language) but you do want to include the
reference
from B, so for example A would be "jobs", D would be "A_range", E would
be
"B_range", and F would be "C_range". Note that these sub-ranges each have
to
have the exact same name except for the letter reference.

Then set your first data validation cell to =jobs. On the cell just to
the
right of that one, put in a vlookup, looking for the source data
validation
value in your range of A1:B3 on the source sheet described above. Make
sure
your last parameter requires an exact match [e.g.
=VLOOKUP(A1,Sheet1!A1:B3,2,FALSE)]. For the sake of this example, let's
say
that you have your main JOBS data validation in A1, and your vlookup in
B1.
Then in your next data validation cell (the sub-list one) make it
=concatenate(B1 & "_range") and it will pull the appropriate sublist each
time the JOBS cell is changed.

Warning: if you change the main jobs selection, it will not automatically
blank out the previous selection in the sub-list, even though the
sub-list
options have changed. There may be a way to do so, but it isn't coming to
me
at the moment.

HTH,
Keith



"Sburlingham" wrote in message
...
ok so i found combobox in the control toolbox however i don't believe
this
is
what i'm looking for. im beginning to think that i'm going to have to
give
a
reference list and have them type what job they did. the other issue is
that
i can't have this sheet(s) be too long i'm looking for the easiest way
using
the least amount of space possible. i'm sorry if im bombarding you and
thank
you for your all your help!!!!

"KR" wrote:

You can set up two separate drop down lists that are linked, if you
use
the
actual controls and not just the within-cell validation. Now that I
think
about it, you could probably use the in-cell validation too, although
I'd
have to think about the details if you had a bunch of rows where you
wanted
to do this selection.

If you set a cell to equal your first combobox value (or first data
validation cell, etc.), then use that as a source for vlookup which
could
return a column letter for your target sub-list. Maybe put this value
in
your third column, so that each of your sub-list selection cells
would
be
able to use it's own pointer to the appropriate sublist. Then set
your
second combobox or data validation cell to a named range that picks
your
sub-list using the "indirect" worksheet function.

That's just off the top of my head, and the details really will
depend
on
whether you are using data validation or comboboxes (and maybe
whether
you
are using comboboxes on the worksheet itself, or on a userform)



"Sburlingham" wrote in
message
...
I have a problem, i created a drop down list of jobs that could
have
been
preformed i need to create another list for each of those jobs to
be
more
specific with what exactly was preformed i had this great idea that
maybe
if
i picked for example warranty work that another list would drop
down
and
let
me pick manufacturing or vendor then another list would drop down
and
i
could
pick what kinda manufact. or vendor warranty it was. this may not
be
possible
though it would be a great thing to implement in the future i need
a
way
that
is user friendly to detail what work was done. any ideas?

Comrade Burlingham,

This is actually pretty simple, it just took me about a half hour to figure
out what the syntax is for the IF formula you put in your secondary (or
tertiary, or quaternary, whatever) dropdown list validation cell.

Example: I have a basic list of general property types: Retail, office and
industrial. In cell B1, I want the user to select one of the 3 types. In
B2, though, I want her to select the SUBtype; i.e., if she selects Retail in
B1, I want her to see only the retail subtypes in B2. If she selects Office
or Industrial in B1, I want her to see only the Office or Industrial subtype
list when she clicks the B2 dropdown. Let's say I've created the four
(General, Retail, Office and Industrial) dropdowns in another sheet in the
same workbook and have given each range the names George, Ronald, Oscar and
Izzy, though giving them the same names as the categories is okay, too).
Then I go back to cell B1 in my main sheet and select Data | Validation
(Excel 2007), choose List and write in the formula, =George. When done, if I
select cell B1, I'll have a dropdown that lists Retail, Office and
Industrial. Then I go to cell B2, select Data | Validation, choose List,
and--here's the secret--enter an IF formula as follows:

=IF(B1="Retail",Ronald,IF(B1="Office",Oscar,IF(B1= "Industrial",Izzy,"Wrongamundo, Buckwheat!")))

There are 3 IF statements, so you have to have 3 closing parens on the end,
of course. But the trick is to identify the text in B1 in quotes (because
text is what Excel will see there), and the named range WITHOUT quotes,
because Excel sees it as a range address, not as text. The formula really
won't return "Wrongamundo, Buckwheat!" if the user fails to select one of the
three types, because those are the only ones I allowed. But I could have
allowed a user-created entry.

Pretty slick, eh? The cool part is you can create as many dropdown levels
as you have levels of patience to enter long, nested "IF" formulas...
__________________________
"Sburlingham" wrote:

I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe if
i picked for example warranty work that another list would drop down and let
me pick manufacturing or vendor then another list would drop down and i could
pick what kinda manufact. or vendor warranty it was. this may not be possible
though it would be a great thing to implement in the future i need a way that
is user friendly to detail what work was done. any ideas?

I found this post very helpful and it answered my questions without posting a
new string. However, I have my first drop down list looking at 20 different
names, and my second dropdown list looking at 20 different lists, contingent
on which name I choose from the first list. There seems to be a limit on how
many levels you can do with the IF function, because I am getting errors when
I try to do this many.

To isolate the problem try this formula in a blank spread sheet, in say cell
B1:

=IF(A1=1,M1:M10,IF(A1=2,N1:N10,IF(A1=3,O1:O10,IF(A 1=4,P1:P10,IF(A1=5,Q1:Q10,IF(A1=6,R1:R10,IF(A1=7,S 1:S10,IF(A1=8,T1:T10,0))))))))

That works but if you add another two "levels":

=IF(A1=1,M1:M10,IF(A1=2,N1:N10,IF(A1=3,O1:O10,IF(A 1=4,P1:P10,IF(A1=5,Q1:Q10,IF(A1=6,R1:R10,IF(A1=7,S 1:S10,IF(A1=8,T1:T10,IF(A1=9,U1:U10,0)))))))))

....You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method to
achieve this? Would the same issues face us if we went to a combobox? (I am
not very familiar with combo boxes) Do you have any remedy for this at all?



"NoBloatwarePlease" wrote:

Comrade Burlingham,

This is actually pretty simple, it just took me about a half hour to figure
out what the syntax is for the IF formula you put in your secondary (or
tertiary, or quaternary, whatever) dropdown list validation cell.

Example: I have a basic list of general property types: Retail, office and
industrial. In cell B1, I want the user to select one of the 3 types. In
B2, though, I want her to select the SUBtype; i.e., if she selects Retail in
B1, I want her to see only the retail subtypes in B2. If she selects Office
or Industrial in B1, I want her to see only the Office or Industrial subtype
list when she clicks the B2 dropdown. Let's say I've created the four
(General, Retail, Office and Industrial) dropdowns in another sheet in the
same workbook and have given each range the names George, Ronald, Oscar and
Izzy, though giving them the same names as the categories is okay, too).
Then I go back to cell B1 in my main sheet and select Data | Validation
(Excel 2007), choose List and write in the formula, =George. When done, if I
select cell B1, I'll have a dropdown that lists Retail, Office and
Industrial. Then I go to cell B2, select Data | Validation, choose List,
and--here's the secret--enter an IF formula as follows:

=IF(B1="Retail",Ronald,IF(B1="Office",Oscar,IF(B1= "Industrial",Izzy,"Wrongamundo, Buckwheat!")))

There are 3 IF statements, so you have to have 3 closing parens on the end,
of course. But the trick is to identify the text in B1 in quotes (because
text is what Excel will see there), and the named range WITHOUT quotes,
because Excel sees it as a range address, not as text. The formula really
won't return "Wrongamundo, Buckwheat!" if the user fails to select one of the
three types, because those are the only ones I allowed. But I could have
allowed a user-created entry.

Pretty slick, eh? The cool part is you can create as many dropdown levels
as you have levels of patience to enter long, nested "IF" formulas...
__________________________
"Sburlingham" wrote:

I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe if
i picked for example warranty work that another list would drop down and let
me pick manufacturing or vendor then another list would drop down and i could
pick what kinda manufact. or vendor warranty it was. this may not be possible
though it would be a great thing to implement in the future i need a way that
is user friendly to detail what work was done. any ideas?

I am trying to do the same thing and the second list will only work for 4 IF
functions, and I have 6. So, how do I get around that limit?

"trexcel" wrote:

I found this post very helpful and it answered my questions without posting a
new string. However, I have my first drop down list looking at 20 different
names, and my second dropdown list looking at 20 different lists, contingent
on which name I choose from the first list. There seems to be a limit on how
many levels you can do with the IF function, because I am getting errors when
I try to do this many.

To isolate the problem try this formula in a blank spread sheet, in say cell
B1:

=IF(A1=1,M1:M10,IF(A1=2,N1:N10,IF(A1=3,O1:O10,IF(A 1=4,P1:P10,IF(A1=5,Q1:Q10,IF(A1=6,R1:R10,IF(A1=7,S 1:S10,IF(A1=8,T1:T10,0))))))))

That works but if you add another two "levels":

=IF(A1=1,M1:M10,IF(A1=2,N1:N10,IF(A1=3,O1:O10,IF(A 1=4,P1:P10,IF(A1=5,Q1:Q10,IF(A1=6,R1:R10,IF(A1=7,S 1:S10,IF(A1=8,T1:T10,IF(A1=9,U1:U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method to
achieve this? Would the same issues face us if we went to a combobox? (I am
not very familiar with combo boxes) Do you have any remedy for this at all?



"NoBloatwarePlease" wrote:

Comrade Burlingham,

This is actually pretty simple, it just took me about a half hour to figure
out what the syntax is for the IF formula you put in your secondary (or
tertiary, or quaternary, whatever) dropdown list validation cell.

Example: I have a basic list of general property types: Retail, office and
industrial. In cell B1, I want the user to select one of the 3 types. In
B2, though, I want her to select the SUBtype; i.e., if she selects Retail in
B1, I want her to see only the retail subtypes in B2. If she selects Office
or Industrial in B1, I want her to see only the Office or Industrial subtype
list when she clicks the B2 dropdown. Let's say I've created the four
(General, Retail, Office and Industrial) dropdowns in another sheet in the
same workbook and have given each range the names George, Ronald, Oscar and
Izzy, though giving them the same names as the categories is okay, too).
Then I go back to cell B1 in my main sheet and select Data | Validation
(Excel 2007), choose List and write in the formula, =George. When done, if I
select cell B1, I'll have a dropdown that lists Retail, Office and
Industrial. Then I go to cell B2, select Data | Validation, choose List,
and--here's the secret--enter an IF formula as follows:

=IF(B1="Retail",Ronald,IF(B1="Office",Oscar,IF(B1= "Industrial",Izzy,"Wrongamundo, Buckwheat!")))

There are 3 IF statements, so you have to have 3 closing parens on the end,
of course. But the trick is to identify the text in B1 in quotes (because
text is what Excel will see there), and the named range WITHOUT quotes,
because Excel sees it as a range address, not as text. The formula really
won't return "Wrongamundo, Buckwheat!" if the user fails to select one of the
three types, because those are the only ones I allowed. But I could have
allowed a user-created entry.

Pretty slick, eh? The cool part is you can create as many dropdown levels
as you have levels of patience to enter long, nested "IF" formulas...
__________________________
"Sburlingham" wrote:

I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe if
i picked for example warranty work that another list would drop down and let
me pick manufacturing or vendor then another list would drop down and i could
pick what kinda manufact. or vendor warranty it was. this may not be possible
though it would be a great thing to implement in the future i need a way that
is user friendly to detail what work was done. any ideas?

I do not think you need to use all the IF statements. You can use the INDIRECT function in your list validation instead which will save you entering all the IF levels. Just give your sub-lists the same names available in the top-level list (e.g. "General", "Retail", "Office" and "Industrial" instead of "George", "Ronald", "Oscar" and "Izzy".)

Example
In the following example the user will have two options available to choose in Cell E1. The options available to select in Cell F1 will depend on what they select in E1.

Cells A1:A2 contain a named range called "WorkType" containing the values "Retail", "Office" (These will be the top level options available to the user)
Cells B1:B2 contain a named range called "Retail" containing the values "Retail option 1" and "Retail option 2". #(These are the second level options that will be available if the user selects "Retail")
Cells C1:C2 contain a named range called "Office" containing the values "Office Option 1" and "Office Option 2". #(These are the second level options that will be available if the user selects "Office")

Cell E1 will be where the user chooses their top level option
Cell F1 will be where the user selects their second level option

For Cell E1 choose DataValidation, select Lists and in the Source box enter =WorkType (including the "=" symbol)

For Cell F1 choose DataValidation, select Lists and in the Source box enter =INDIRECT($E1) (including the "=" symbol again)

If the user selects "Retail" in E1 then the drop down list in F1 will contain "Retail Option 1" and "Retail Option 2", but if the user selects "Office" in E1 then the F1 list will contain "Office Option 1" and "Office Option 2"






----------------
Now playing: Neil Diamond - Captain Of A Shipwreck
via FoxyTunes

"KR" wrote:

Try this, from which you should be able to build what you want.

On sheet1, set up the source lists for validation:
A B C D E F
Sow A 1 Get Seed Fill tank Get rake
Mow B 2 Fill hopper Start mower rake piles
Rake C 3 Spread Seed Mow yard bag piles


A is your main list (job) and D,E,F are your potential sub-lists for each
job. C is just for hours spent, which would be just a normal data validation
list with no dependencies.

Name your ranges for A, D, E, F. You have to use something that isn't a
keyword in Excel (in any language) but you do want to include the reference
from B, so for example A would be "jobs", D would be "A_range", E would be
"B_range", and F would be "C_range". Note that these sub-ranges each have to
have the exact same name except for the letter reference.

Then set your first data validation cell to =jobs. On the cell just to the
right of that one, put in a vlookup, looking for the source data validation
value in your range of A1:B3 on the source sheet described above. Make sure
your last parameter requires an exact match [e.g.
=VLOOKUP(A1,Sheet1!A1:B3,2,FALSE)]. For the sake of this example, let's say
that you have your main JOBS data validation in A1, and your vlookup in B1.
Then in your next data validation cell (the sub-list one) make it
=concatenate(B1 & "_range") and it will pull the appropriate sublist each
time the JOBS cell is changed.

Warning: if you change the main jobs selection, it will not automatically
blank out the previous selection in the sub-list, even though the sub-list
options have changed. There may be a way to do so, but it isn't coming to me
at the moment.

HTH,
Keith


HI, I have tried the above but when I go to create the drop down list using
the data validation tool and enter "=CONCATENATE(B8 & "_range")" I get an
error message saying that "the list source must be a delimited list, or a
reference to aa single row or column" what does this mean???

Thanks

HShanker

I, too, am attempting to nest a drop down list within in a validated list;
however, what I would really like to accomplish is this two-tier selection
within one cell. I liken this to a two-tier drop down menu, whereby you
select one item from a menu and a fly-out menu appears, from which you can
select one item (which carries out the appropriate function).

Specifically, in excel, I have a select set of 60 tasks. For 10+ of these
tasks, I have 5 subtasks. In a validated cell, I select any of the 10+
'master' tasks, I would like to have another fly-out list of options appear
(i.e. a list of the 5 subtasks). Upon selecting one of these subtasks, I
would ideally like to have a concatenation of the master task and subtask
appear in the cell (e.g. Task1:substask3). Is this type of programming
possible in Excel? I do not have the option of two different entries in
separate cells (i.e. as described in previous posts with dependent list
solutions). I hope this inquiry makes sense. I welcome your input!!!

"Sburlingham" wrote:

I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe if
i picked for example warranty work that another list would drop down and let
me pick manufacturing or vendor then another list would drop down and i could
pick what kinda manufact. or vendor warranty it was. this may not be possible
though it would be a great thing to implement in the future i need a way that
is user friendly to detail what work was done. any ideas?

http://www.contextures.com/tiptech.html


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LHall" wrote in message
...
I, too, am attempting to nest a drop down list within in a validated list;
however, what I would really like to accomplish is this two-tier selection
within one cell. I liken this to a two-tier drop down menu, whereby you
select one item from a menu and a fly-out menu appears, from which you can
select one item (which carries out the appropriate function).

Specifically, in excel, I have a select set of 60 tasks. For 10+ of these
tasks, I have 5 subtasks. In a validated cell, I select any of the 10+
'master' tasks, I would like to have another fly-out list of options
appear
(i.e. a list of the 5 subtasks). Upon selecting one of these subtasks, I
would ideally like to have a concatenation of the master task and subtask
appear in the cell (e.g. Task1:substask3). Is this type of programming
possible in Excel? I do not have the option of two different entries in
separate cells (i.e. as described in previous posts with dependent list
solutions). I hope this inquiry makes sense. I welcome your input!!!

"Sburlingham" wrote:

I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe
if
i picked for example warranty work that another list would drop down and
let
me pick manufacturing or vendor then another list would drop down and i
could
pick what kinda manufact. or vendor warranty it was. this may not be
possible
though it would be a great thing to implement in the future i need a way
that
is user friendly to detail what work was done. any ideas?

NoBloatwarePlease,

You made my day today. I had this problem disturbing me for the whole of
last week.
Keep up the good work.

Regards,
Jerome

"NoBloatwarePlease" wrote:

Comrade Burlingham,

This is actually pretty simple, it just took me about a half hour to figure
out what the syntax is for the IF formula you put in your secondary (or
tertiary, or quaternary, whatever) dropdown list validation cell.

Example: I have a basic list of general property types: Retail, office and
industrial. In cell B1, I want the user to select one of the 3 types. In
B2, though, I want her to select the SUBtype; i.e., if she selects Retail in
B1, I want her to see only the retail subtypes in B2. If she selects Office
or Industrial in B1, I want her to see only the Office or Industrial subtype
list when she clicks the B2 dropdown. Let's say I've created the four
(General, Retail, Office and Industrial) dropdowns in another sheet in the
same workbook and have given each range the names George, Ronald, Oscar and
Izzy, though giving them the same names as the categories is okay, too).
Then I go back to cell B1 in my main sheet and select Data | Validation
(Excel 2007), choose List and write in the formula, =George. When done, if I
select cell B1, I'll have a dropdown that lists Retail, Office and
Industrial. Then I go to cell B2, select Data | Validation, choose List,
and--here's the secret--enter an IF formula as follows:

=IF(B1="Retail",Ronald,IF(B1="Office",Oscar,IF(B1= "Industrial",Izzy,"Wrongamundo, Buckwheat!")))

There are 3 IF statements, so you have to have 3 closing parens on the end,
of course. But the trick is to identify the text in B1 in quotes (because
text is what Excel will see there), and the named range WITHOUT quotes,
because Excel sees it as a range address, not as text. The formula really
won't return "Wrongamundo, Buckwheat!" if the user fails to select one of the
three types, because those are the only ones I allowed. But I could have
allowed a user-created entry.

Pretty slick, eh? The cool part is you can create as many dropdown levels
as you have levels of patience to enter long, nested "IF" formulas...
__________________________
"Sburlingham" wrote:

I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe if
i picked for example warranty work that another list would drop down and let
me pick manufacturing or vendor then another list would drop down and i could
pick what kinda manufact. or vendor warranty it was. this may not be possible
though it would be a great thing to implement in the future i need a way that
is user friendly to detail what work was done. any ideas?