Copy rows to different sheet in condition is met

[Milgod]

Good afternoon,

I'm in need to a little help if possible. I have a worksheet that has names down column A8:A41. There are other columns below that are working some things out for me, but I don't need that copied. In the cells (B7:F7) there are different headings - SE, EA, PP, LA, HA.

What I need is a way to copy an entire row depending on what I enter in the columnss below those headings. For instance, if for the first person (name in cell A8) I enter Y in cell B8, I want the entire row to copy to worksheet 2. This worksheet 2 also has the same headings and set up. All looks the same, but it is blank at the moment.

So there might only be 3 rows that have Y entered in the SE column - I'd want them to fill up at the top of the worksheet 2 - as opposed to their row they're in on sheet 1.

I'd want to do this for the other headings as well.

As an extra, if no 'Y' is entered, I'd want those rows that are left blank to copy to a different worksheet.

I guess this would be a VBA, but I'm not very good at creating these yet. I hope I explained myself (probably not). If anyone can help or point me in the right direction, I'd be very appreciative.

[JBeaucaire]

This CAN be done for a such a short range using Formulas.

1) Sheet2 should have an extra column where you can only use the array formula once per row to flag a row on sheet1 as pertinent. Then that row number is used to fill in the rest of the fields on that row for you using a normal INDEX/MATCH.

2) Attach a sample workbook. Make sure there is just enough data to demonstrate your need. IMake sure your desired results are shown, mock them up manually from this sample data so it's visually clear.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Milgod]

Here is a sample of what I'm thinking.

I have done the first few sheets. The others will follow the same idea. Hope it makes sense. If there is a Y in the column - I want the row copied to the corresponding sheet. If there isn't, then there is a 'non' sheet I want those names on.

[JBeaucaire]

1) A key array formula in AF8 collects the index position of matching rows.
=IFERROR(SMALL(IF(Sheet1!$B$8:$B$41="Y", ROW(Sheet1!$B$8:$B$41)-7, ""), ROW(A1)), "")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

2) Copy AF8 down through AF41 to complete the array.

3) The formula in A8 is:
=IF(ISNUMBER($AF8), INDEX(Sheet1!A$8:A$41, $AF8)&"", "")

3) Copy A8 down and across the table to all needed cells.

SE is now complete.

4) Copy A7:AF41 and paste it over NON-SE at cell A7

5) Edit the "Y" in the AF8 formula to "", then copy AF8 down.

NON-SE is now complete.


Repeat ad nauseum.