Excel Urgent Help

[Sourav Mukherjee]

Dear All,

There are two column mentioned below

[TABLE="width: 268"]
<tbody>[TR]
[TD]Animal Name[/TD]
[TD][/TD]
[TD]Animal Type[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the column named "Animal Name" there is data validation of all Animal like[TABLE="width: 72"]
<tbody>[TR]
[TD]Goat[/TD]
[/TR]
[TR]
[TD]Cow[/TD]
[/TR]
[TR]
[TD]Horse
[TABLE="width: 102"]
<tbody>[TR]
[TD]Lion[/TD]
[/TR]
[TR]
[TD]Tiger[/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
There are two other column
[TABLE="width: 246"]
<tbody>[TR]
[TD="colspan: 3"]Types OF Animal[/TD]
[/TR]
[TR]
[TD]Vegiterian[/TD]
[TD][/TD]
[TD]Non Vegiterian[/TD]
[/TR]
[TR]
[TD]Goat[/TD]
[TD][/TD]
[TD]Lion[/TD]
[/TR]
[TR]
[TD]Cow[/TD]
[TD][/TD]
[TD]Tiger[/TD]
[/TR]
[TR]
[TD]Horse[/TD]
[TD][/TD]
[TD]Fox[/TD]
[/TR]
</tbody>[/TABLE]

Now My concern is whenever I select an animal name from the list in the "Animal Name" tab in the next cell It need to show the animal type like this.

[TABLE="width: 268"]
<tbody>[TR]
[TD]Animal Name[/TD]
[TD][/TD]
[TD]Animal Type[/TD]
[/TR]
[TR]
[TD]Goat[/TD]
[TD][/TD]
[TD]Vegiterian[/TD]
[/TR]
[TR]
[TD]Lion[/TD]
[TD][/TD]
[TD]Non Vegiterian[/TD]
[/TR]
</tbody>[/TABLE]





Thanks

 

[mumps]

Just to clarify ... Is the drop down list (validation) on one sheet and the animal types on a different sheet? If so, in which cell is the drop down list and in which columns are the animal types?

 

[Sourav Mukherjee]

Just to clarify ... Is the drop down list (validation) on one sheet and the animal types on a different sheet? If so, in which cell is the drop down list and in which columns are the animal types?

Thank You for you reply

(Sheet 2)
Hear we have the original list of all animal which is validated on Sheet 1 and the "Types Of animal",

(Sheet 1)
"Animal Name" is located on "A3" cell this is a drop-down list(Data Validation) when ever we select a animal from this list It Need to show the "Animal Type" in "B3" cell.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your selection in cell A3. I'm assuming that the "Types Of animal" are in columns A and B of Sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Set foundVal = Sheets("Sheet2").Range("A:A,B:B").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Range("B3") = Sheets("Sheet2").Cells(1, foundVal.Column)
    Else
        MsgBox ("Animal not found.")
    End If
    Application.ScreenUpdating = True
End Sub