Results 1 to 7 of 7

Thread: If Statement with loop/repeat

  1. #1

    If Statement with loop/repeat

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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)
    Y Joe
    N Frank
    Y April
    N Sandy
    Y Rick
    N Sam
    Y Terri

    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.

    Joe April Rick Terri
    Planning & Organizing
    Quality and Quantity of Work
    Knowledge and Skills
    Inititative & Innovation

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

  2. #2
    Good afternoon,

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

    Attached Files Attached Files

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


  4. #4
    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,

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

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

  7. #7
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts