Can this be done without a macro?

[mighty mouse]

Wondering how to do the following (formula-based); hope this makes some sense!

-I have a list of customers that I receive reports from in column A.
-When I receive a report from a customer I put an "x" in column D to show report was received that week.
-However, next to column A is another name in column B that the report must be forwarded to. Not all the names in column A have a name in column B that reports have to be forwarded to, but there are some.
-I'm looking to put the names of those whom reports must be forwarded to on sheet2, and under each of those names a list forms as I place an "x" onto sheet1.

Example:

Cell A3 of sheet1 says "Smith"
I receive a report from Smith, so I put an "x" in cell D3.
Cell B3 says Anderson, meaning that if I receive a report from Smith, it needs to be forwarded to Anderson.
In cell A3 of sheet2, it says Anderson-and below that name I need to form a list of all reports that will need to be forwarded to Anderson. Smith would appear under Anderson's name in sheet2.

Thanks for any assistance!

 

[venkat1926]

see sample sheet1 below and run the macro on that.
see what happens in sheet2

if it is ok modify to suit you

the macro is

Sub testone()
With Worksheets("sheet1")
.UsedRange.AutoFilter

Dim reportto As Range
Dim reportfrom As Range
Set reportto = .Cells.Find(what:="x", lookat:=xlWhole)

Set reportto = reportto.Offset(0, -2)
Set reportfrom = reportto.Offset(0, -1)
    .UsedRange.AutoFilter Field:=2, Criteria1:=reportto.Value, Operator:=xlAnd
 .UsedRange.SpecialCells(xlCellTypeVisible).Copy
       End With
        With Worksheets("sheet2")
     
        .Range("a2").PasteSpecial
     
        End With
        With Worksheets("sheet1")
.UsedRange.AutoFilter

End With


End Sub

 

[mordrid]

I notice you do not want to use a macro, this formula plus filtering on non blanks will give you what you want I think.

Someone else may be able to tell you how without getting the blank rows

IF(Sheet1!D3="x", IF(Sheet1!B3=$A$3,Sheet1!A3,""),"")

If you place this in A4 on sheet 2 and then copy it down as many rows as you want it will place the name from the corresponding row on Sheet 1 so if B4 and B7 on sheet1have the name anderson in (as in Sheet 2 A3) and D4 and D7 on Sheet 1 have x in them then cells A4 and A7 will have the names from Sheet 1 A4 and A7 in them. If you then apply auto filter on non blanks the blank rows in between will be hidden.

Hope this helps you get to where you want to be