Copy row from Sheet 1 to Sheet 2 when a cell in Column E reads "Sent Out".

[dcairo]

I have a workbook with 2 work sheets. The first sheet is a list of all items with a status of either "Sent Out", "Job Won" or "Job Lost". I want to take any row that has a status of "Sent Out" and copy it to sheet 2. Likewise, when I update sheet 1 from "Sent Out" to either "Job Won" or "Job lost, I want that row to be deleted from sheet 2 (making sheet 2 solely those with a status of "Sent Out").

To go even further, I would like the copied data to be put under the correct customer name heading in sheet 2 when it is copied over.

[daffodil11]

This can definitely be done.

A3:

=INDEX(Master!A$2:A$190,SMALL(IF(Master!$B$2:$B$190='Sent Out'!$A$1,IF(Master!$E$2:$E$190="Sent Out",ROW(Master!$A$1:$A$189))),ROW(A1)))

B3:

=INDEX(Master!B$2:B$190,SMALL(IF(Master!$B$2:$B$190='Sent Out'!$A$1,IF(Master!$E$2:$E$190="Sent Out",ROW(Master!$A$1:$A$189))),ROW(B1)))

C3:

=INDEX(Master!D$2:D$190,SMALL(IF(Master!$B$2:$B$190='Sent Out'!$A$1,IF(Master!$E$2:$E$190="Sent Out",ROW(Master!$A$1:$A$189))),ROW(D1)))

D3:

=INDEX(Master!E$2:E$190,SMALL(IF(Master!$B$2:$B$190='Sent Out'!$A$1,IF(Master!$E$2:$E$190="Sent Out",ROW(Master!$A$1:$A$189))),ROW(E1)))

E3:

=INDEX(Master!F$2:F$190,SMALL(IF(Master!$B$2:$B$190='Sent Out'!$A$1,IF(Master!$E$2:$E$190="Sent Out",ROW(Master!$A$1:$A$189))),ROW(F1)))


Note: Each of these is an array formula, confirmed with Ctrl+Shift+Enter instead of just hitting Enter to exit the cell.

Then drag all 5 down. Note that C3's formula omits the index of column C, since you aren't showing the Contact's name.
For each successive block (Customer) you'll use the same 5 starting formulas, but set the =A1 to the next Customer Name. (The second block's Customer name is in A10.)

[dcairo]

Thank you...and it looks great! ...until I touch it. I have been trying to figure out how to finish it with no luck.. Can you take a look at the next customer for me?

[daffodil11]

I see. The references of the formulas are adjusting themselves.

In order to prevent this, your best bet is to click A3, then click F2 to enter the cell, select the formula with your cursor, Ctrl+C to copy it, then hit Escape.

Then go into the cell in A14, and hit Ctrl+V to paste A3's actual formula. Then just reposition =A1 to =A12 so it points to Brivar instead of JB Donaldson. Now you can copy it downwards.