If Statement with loop/repeat

75VHSAlum

New member
Joined
Sep 18, 2013
Messages
7
Reaction score
0
Points
0
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)
YJoe
NFrank
YApril
NSandy
YRick
NSam
YTerri

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.

JoeAprilRickTerri
Planning & Organizing
Quality and Quantity of Work
Dependability
Knowledge and Skills
Inititative & Innovation

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

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good afternoon,

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

Thanks,
 

Attachments

  • Copy_if_Example.xlsx
    9.4 KB · Views: 28

75VHSAlum

New member
Joined
Sep 18, 2013
Messages
7
Reaction score
0
Points
0
I believe you got it!
I understand what you did on sheet 1, but could you explain the formula on sheet 2.

Thanks,
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
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,
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Excellent; that's what I like to hear :). You're very welcome.
 

75VHSAlum

New member
Joined
Sep 18, 2013
Messages
7
Reaction score
0
Points
0
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.
 
Top