data validation list automatically shows the first item in the list

[soniaalalwani]

Hi,


I am trying to create two Data Validation lists (one dependent on the other). when i change the value in the first drop down i would like the other drop down to either show a blank or one of the items in the list.

Eg.

If the first drop down is a country and other is cities

India (in the first drop down) Delhi( in the second drop down). Now when i change India to say USA the cell next to it which shows Delhi should either change to one of the cities of USA or become blank.


I tried finding the solution on different forums but most of the solutions were in VBA. Can it be done without coding in VBA?????

 

[Luke M]

No, it can not be done w/o VB because the cell with the dropdown is holding a regular text string w/o a formula, and only a formula would be able to change its value based on the change in another cell.

 

[soniaalalwani]

Dont we have any formula where we can put in the data validation form that comes up. Where we put the formula=INDIRECT() in place of this cant we use any formula for the same?

And if no, can anyone help me with the VBA code for the same.

 

[Luke M]

I think this will work for you. Just need to define where your two ranges are at.

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyCell As Range
Dim DepList As Range

'Which cell has first drop down?
Set MyCell = Range("B2")

'which cell has dependent list?
Set DepList = Range("C2")
If Intersect(MyCell, Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

Dim NewValue As String
NewValue = Target.Value

Application.Undo
DepList.Value = WorksheetFunction.Index(Range(NewValue), WorksheetFunction.Match(DepList.Value, Range(Target.Value), 0))
Target.Value = NewValue

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

[/pre]

 

[SirJB7]

Hi, soniaalalwani!


Give a look at this file:

http://dl.dropbox.com/u/60558749/data%20validation%20list%20automatically%20shows%20the%20first%20item%20in%20the%20list%20%28for%20soniaalalwani%20at%20chandoo.org%29.xlsx


It handles a two dropdown lists varying the second one upon the selection made on first one. The only disadvantage is that does not change automatically the previously selected value in the second list until you click and displays it.


I don't not if it will be suitable for your requirements.


Regards!

 

[soniaalalwani]

@SirJB7


Well my problem is when i change the country automatically the city cell should either become blank or it should reflect a city from the country. that is not happening and i would really appreciate if someone could help me do it without using VBA

 

[SirJB7]

Hi, sonialalwani!


The same previous file with this little addition of VBA code:

-----

[pre]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$1" Then Range("J1").Value = ""
End Sub

[/pre]
-----


The link:

http://dl.dropbox.com/u/60558749/data%20validation%20list%20automatically%20shows%20the%20first%20item%20in%20the%20list%20%28for%20soniaalalwani%20at%20chandoo.org%29.xlsm


This just do the work.

If "it'ok" then "fine" else "sorry, magicians don't work today" endif


Regards!

 

[GN0001]

I opened the links you and others have sent, I couldn't find any solution.

Regards,

GGGGG

 

[SirJB7]

@GGGGG

Hi!

The last link is to a workbook that has a 2 level of chained drop down list boxes.

First level, country (3 entries in the example). Column A.

Second level, city (3, 2, 4, entries). Columns B:D.

First drop down list box, G1. Yellow shaded.

Second drop down list box, J1. Idem.

Regards!

 

[GN0001]

I saw that, thanks, how is it working? does this involve any VBA coding? I have 16000 rows of physicians.


End user selects the state, only the Physicians for that state will be shown on second combo box. I need combo box rather than drop down list.


Then there are several other combo boxes ( 6 combo boxes) that sumifs returns the value with picking up the value from these combo boxes.

 

[Abhijeet R. Joshi]

Hi Sonia/GN0001,

You firstly need to name the range of the dependent values and then use the INDIRECT formula in the list option of the data validation (using the parent validation cell in the INDIRECT formula).
For instance, Col.D have Company and Column E has Company1 Model and Col.F has Company2 Models and you have a validation in B2 and dependent validation in Cell B3.

Here, my parent validation is in cellB2(company) and cellB3 (Model) has my dependent validation.
So for parent validation I will manually select the validation values from the list options whereas for the dependent validation I will say=INDIRECT($B$2) in the list option of data validation.

Hope this helps....

Thanks,
Abhi