How to create sub-lists?

[AA Arens]

Sheet with tasks and subtasks from employees.

I do have two colums. The first I choose a task from a list. Then I
want to choose a sub-task from the second column and this subtask
depends on the task choosen in the first column. How to perform it?

At this moment I do only have the task list (with name), on another
worksheet.

Bart
Ex 2003.

 

[Debra Dalgleish]

You can use dependent data validation lists, as described here:

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

 

[AA Arens]

Thanks Debra, tht was very usefull.
Have a question.
When I choose another value from the first list, the value in the
second list what still belongs to another category, still remains in
the call. How can I have it automatically cleared when I choose another
vlaue in the first list?

Bartt

 

[Debra Dalgleish]

You could use data validation in both columns to prevent invalid
selections. There's a sample here:

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

Under Data Validation, look for 'Dependent Lists Country City'

 

[AA Arens]

I choosed the code of the UpdateDependent.xls to have the value of the
sublist changed when I choose another value in the 1st list. To have it
cleared, I just have all sublists starting with a blank cell.

I also have two words in my 1st list and choosed for the SUBSTITUE
command in http://www.contextures.com/xlDataVal02.html.

Although, I am still able to choose a value in the sublist, after a two
word value in the 1st list, I still get the error "Coul not change
dependend cell", from the code. How to bypass it?

Bart

 

[AA Arens]

My 2nd question is, I want to have the lists as blank value in starting
position. So, the 1st list starts with an empty cell. Then I also get
the eror from the code "Coul not change dependend cell",. How to avoid
this? I am not albe to make a blank name of a blank sublist.

 

[Guest]

AA Arens:

Instead of heading each list with a blank cell, try this:

Replace this line in the VBA code (which selects the 1st list item):
Me.Range("D2").Value = rng.Offset(0, 0).Value

with this (which clears the dependent list cell):
Me.Range("D2").ClearContents

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP

 

[AA Arens]

That clears the cell, but when I want to choose a value the starting
position is at the bottom of the dropdown list. Thats why I prefer the
Offset.

Me.Range("D2").ClearContents followed by
Me.Range("D2").Value = rng.Offset(0, 0).Value

does not solve the problem.

Any other idea?

 

[AA Arens]

Unfortunately only the first 4 rows works finely when I copied the part
25X as I do have 25 rows with the list.

This is the code:

If Not Intersect(Target, Me.Range("B10")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set rng = ActiveWorkbook.Names(Target.Value).RefersToRange
Me.Range("C10").Value = rng.Offset(0, 0).Value
End If

And that 25 x for B11/C11, B12/C12 etc.

 

[Guest]

AA Arens:

Sorry, but I can't duplicate what you're describing.

Using Debra Dalgleish's UpdateDependent model with the VBA code change I
posted earlier, I added 30 items to the CITY list.

Any change to the parent list cleared the dependent list. Clicking on the
dependent list simply displays the dropdown, listing the items beginning at
the top,

***********
Regards,
Ron

XL2002, WinXP

 

[AA Arens]

Oops, forgot an "End If" at the 5th section while copy/pasting 25X.
Thanks.

Regarding my earlier posting, I use one word (with "_") in the first
list so I don't use the substitute command.