How Display Excel rows on selection of first Excel Column Value

Question

Hi,

I have Two Excel sheets. My requirement is when i select Reason Value From First Excel sheet Reason Column. It will display respected Reason Value in Second Excel.

So using Macro i want to Display Second Excel Rows on selection of reason in first excel.

Please Help.

First Excel is And Reason Column Contains Reason1 , Reason 2 etc.

This Second Excel Data

Answer 1

Here's something that may get you started. (I think this is close to what you would like to do.)

Create a named range for the data on the second sheet. Named "new_range" in my example.

Then create the following procedure in a new module:

Sub FilterSheetTwo()

    Worksheets("Sheet2").Range("new_range").AutoFilter Field:=6, Criteria1:="Reason1"

End Sub

When you run this procedure, it should filter the results on Sheet2.

You can then hook this procedure up to an Worksheet_Change event on Sheet1.

Answer 2

If you can sort your Reason column on the second sheet and place it as the left-most column you don't need a macro--you can do this using VLOOKUP. Steps:

1. Sort Data by your Reason column on the second sheet.

2. In each column of the first sheet enter the following formula:

   =VLOOKUP(E2, DataRangeOfSheet2, ColumnYouWantFromDataRange)
   

See VLOOKUP for more info.