Hi All

I have two lists... depending upon the selction of one list I want to
update another list...
e.g. Country List - Here I am listing UK, Ireland
My second List contains Cities, i.e. London, Manchester, Leeds, Dublin etc...

Now if I select UK in Country list I want to display only UK cities in City
list (i.e. London, Machester, Leeds etc) and If I select Ireland then only
Ireland cities (i.e. Dublin etc)

I have implemeted this functionality creating Named ranges... have created
3 lists, gave them name, and used if condition... everything is working
fine... but issue is earlier selection...

If I Select UK... obviously my city list is currently displaying only UK
cities... here if I am selecting lets say London... now when I select Ireland
in country list... my city list is getting updated with Ireland cities but my
previous selection i.e London is still there... I don't want to display
london when I select Irelant... i want to set it either to any default
value... or blank... but certainly not London...

do I need to write a code to do this?? if yes how? bec's macro is not
identifying list?? and what would be the trigger function??

Please help... lokking forward for your prompt reply... thanks

Milind

Correct, you will need a VB event macro. Right click on sheet tab, view code.
Paste this in, modifying as needed. Note that this will make the second cell
blank.

Private Sub Worksheet_Change(ByVal Target As Range)

'Change to cell with first list
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub

'Change to cell with second list
Range("A3").ClearContents
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Milind Keer" wrote:

Hi All

I have two lists... depending upon the selction of one list I want to
update another list...
e.g. Country List - Here I am listing UK, Ireland
My second List contains Cities, i.e. London, Manchester, Leeds, Dublin etc...

Now if I select UK in Country list I want to display only UK cities in City
list (i.e. London, Machester, Leeds etc) and If I select Ireland then only
Ireland cities (i.e. Dublin etc)

I have implemeted this functionality creating Named ranges... have created
3 lists, gave them name, and used if condition... everything is working
fine... but issue is earlier selection...

If I Select UK... obviously my city list is currently displaying only UK
cities... here if I am selecting lets say London... now when I select Ireland
in country list... my city list is getting updated with Ireland cities but my
previous selection i.e London is still there... I don't want to display
london when I select Irelant... i want to set it either to any default
value... or blank... but certainly not London...

do I need to write a code to do this?? if yes how? bec's macro is not
identifying list?? and what would be the trigger function??

Please help... lokking forward for your prompt reply... thanks

Milind

Hi Luke

Thanks for the prompt reply... I thought about this option but on any change
this function is getting called and and resetiing the values... which I don't
want....

Basically what I want is... If I select UK... then other other list box
should retain the value if it belongs to UK... but if I select Ireland then
if UK city is already there in second list box it should updated to blank....
i know this is something possible thr' coding but don;t know which event
should I use....

Is there any event for Dropdown list...??

-milind





"Luke M" wrote:

Correct, you will need a VB event macro. Right click on sheet tab, view code.
Paste this in, modifying as needed. Note that this will make the second cell
blank.

Private Sub Worksheet_Change(ByVal Target As Range)

'Change to cell with first list
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub

'Change to cell with second list
Range("A3").ClearContents
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Milind Keer" wrote:

Hi All

I have two lists... depending upon the selction of one list I want to
update another list...
e.g. Country List - Here I am listing UK, Ireland
My second List contains Cities, i.e. London, Manchester, Leeds, Dublin etc...

Now if I select UK in Country list I want to display only UK cities in City
list (i.e. London, Machester, Leeds etc) and If I select Ireland then only
Ireland cities (i.e. Dublin etc)

I have implemeted this functionality creating Named ranges... have created
3 lists, gave them name, and used if condition... everything is working
fine... but issue is earlier selection...

If I Select UK... obviously my city list is currently displaying only UK
cities... here if I am selecting lets say London... now when I select Ireland
in country list... my city list is getting updated with Ireland cities but my
previous selection i.e London is still there... I don't want to display
london when I select Irelant... i want to set it either to any default
value... or blank... but certainly not London...

do I need to write a code to do this?? if yes how? bec's macro is not
identifying list?? and what would be the trigger function??

Please help... lokking forward for your prompt reply... thanks

Milind

Hey Luke...

I resolved this issue...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 3 And Target.Column = 3 Then
If Range("C3").Value = "IRE" Then
Range("C4").Value = "Dublin"
End If

If Range("C3").Value = "UK" And Range("C4").Value = "Dublin" Then
Range("C4").Value = "London"
End If
Else
Exit Sub
End If

End Sub


I will make more robust... but its working.... thanks a ton...





"Milind Keer" wrote:

Hi Luke

Thanks for the prompt reply... I thought about this option but on any change
this function is getting called and and resetiing the values... which I don't
want....

Basically what I want is... If I select UK... then other other list box
should retain the value if it belongs to UK... but if I select Ireland then
if UK city is already there in second list box it should updated to blank....
i know this is something possible thr' coding but don;t know which event
should I use....

Is there any event for Dropdown list...??

-milind





"Luke M" wrote:

Correct, you will need a VB event macro. Right click on sheet tab, view code.
Paste this in, modifying as needed. Note that this will make the second cell
blank.

Private Sub Worksheet_Change(ByVal Target As Range)

'Change to cell with first list
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub

'Change to cell with second list
Range("A3").ClearContents
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Milind Keer" wrote:

Hi All

I have two lists... depending upon the selction of one list I want to
update another list...
e.g. Country List - Here I am listing UK, Ireland
My second List contains Cities, i.e. London, Manchester, Leeds, Dublin etc...

Now if I select UK in Country list I want to display only UK cities in City
list (i.e. London, Machester, Leeds etc) and If I select Ireland then only
Ireland cities (i.e. Dublin etc)

I have implemeted this functionality creating Named ranges... have created
3 lists, gave them name, and used if condition... everything is working
fine... but issue is earlier selection...

If I Select UK... obviously my city list is currently displaying only UK
cities... here if I am selecting lets say London... now when I select Ireland
in country list... my city list is getting updated with Ireland cities but my
previous selection i.e London is still there... I don't want to display
london when I select Irelant... i want to set it either to any default
value... or blank... but certainly not London...

do I need to write a code to do this?? if yes how? bec's macro is not
identifying list?? and what would be the trigger function??

Please help... lokking forward for your prompt reply... thanks

Milind