"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