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?