If Statement with loop/repeat

Using Excel 2007.
I have a workbook where on sheet 1 if have the following data (a1:b27)
(Sheet 1 has 27 rows of data)

On Sheet1 above, if Column A equals "Y", I want to copy the name to Sheet2 below. If Column A equals "N", I go to the next row and check if Column A equals "Y", if so, copy the name to Sheet 2, if equals "N" go to the next row and continue to check and copy.
I only want to copy over to Sheet 2 the name if Column A equals "Y".
So utilizing my data from Sheet 1, Sheet 2 should look like below.


How can I achieve this without using Nested If Statements?
Any help would be appreciated!

Good afternoon,

I think this is what you're after. Let me know if I'm off target.

Thanks,

I believe you got it!
I understand what you did on sheet 1, but could you explain the formula on sheet 2.

Thanks,

Certainly. So basically it's an index-match lookup with a volatile element. "Column" returns the numerical *value* of the column it is currently in (a = 1, b = 2, etc). Match looks up a value and returns the row number. So, match(column(),--range--) will find the column number that the formula is in (say 3) in a list of values. Index provides the answer. So you end up with =index(--answer range--,match(--whatever column I'm in--,--lookup range--, 0) (0 means exact match, versus -1 or 1 - greater than/less than).

Hope this helps,

That makes it clear.
Thank you very much!!!!!

Excellent; that's what I like to hear . You're very welcome.

Need some help. I used this formula and it works perfectly. But I have ran into a problem.
Seems that on worksheet 1 they enter data into the fields and then employees if they make a mistake delete the data by selecting the whole row and deleting.
by doing this it is deleting my formula. I need to adjust the formula on sheet 1 column C to a location farther down on the sheet. I have experimented but no luck.