How To Display Excel rows on selection of first Excel Column Value

Question

I have Two Excel sheets. My requirement is when I select a reason value from one Excel sheet Reason_Name column, it will display that reason value in a second Excel sheet.

So using Macro, I want to display the second Excel rows on selection of reason in first Excel.

Please Help.

This is the first Excel sheet - Reason_Name column contains Reason1, Reason2, etc.

alt text http://www.freeimagehosting.net/uploads/a10d6be7a5.png

This is the second Excel sheet

alt text http://www.freeimagehosting.net/uploads/99e0ff4cdb.png

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.