Excel Urgent Help

[Sourav Mukherjee]

Dear All,

There are two column mentioned below

Animal Name		Animal Type

<tbody>
</tbody>

In the column named "Animal Name" there is data validation of all Animal like

Goat

Cow

Horse Lion Tiger Fox <tbody> </tbody>

<tbody>
</tbody>

There are two other column

Types OF Animal
Vegiterian		Non Vegiterian
Goat		Lion
Cow		Tiger
Horse		Fox

<tbody>
</tbody>

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.

Animal Name		Animal Type
Goat		Vegiterian
Lion		Non Vegiterian

<tbody>
</tbody>

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