+ Reply to Thread
Results 1 to 12 of 12

If Function is False continue to check row below for true value

  1. #1
    Registered User
    Join Date
    03-25-2013
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2010
    Posts
    10

    If Function is False continue to check row below for true value

    I have master list of names and whether they ordered 1 of 3 products (Year, Half, Short). Then I have 3 tabs to the right for each of thee 3 products. I want to be able to create an if function for each of these 3 tabs which will formulate the appropriate names. For example, If John Doe ordered the yearly product, I want his name to be able to show up in the first cell of the year tab. Then, if the second person to order on the master list ordered for short-term, I want their name to populate on the first row of the short-term tab. Does this make sense? If so, is this possible?

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If Function is False continue to check row below for true value

    This thread shows how to do what you want. See if you can adapt it to your needs.

    http://www.excelforum.com/excel-form...-function.html
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-25-2013
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: If Function is False continue to check row below for true value

    Updated Below
    Last edited by BearsBikes; 03-25-2013 at 11:14 PM.

  4. #4
    Registered User
    Join Date
    03-25-2013
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: If Function is False continue to check row below for true value

    Updated Below
    Last edited by BearsBikes; 03-25-2013 at 11:15 PM.

  5. #5
    Registered User
    Join Date
    03-25-2013
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: If Function is False continue to check row below for true value

    Okay so here's another update. This formula works for the first row but for some reason isn't working when I copy it down and isnt working for the other two tabs in which the first entry for that product isnt in the first row.

    =IFERROR(INDEX('Renter Info'!D2:D200,SMALL(IF('Renter Info'!C2:C12="Year",ROW('Renter Info'!D2:D200)),ROWS('Renter Info'!$D2:'Renter Info'!D2))-ROW('Renter Info'!C$2)+1),"")

  6. #6
    Registered User
    Join Date
    03-25-2013
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: If Function is False continue to check row below for true value

    Got the formula to work but only 1 problem. I was looking for a formula that wouldn't leave any cells blank. For example, if 2 people who ordered Yearly product were 2nd and 6th in the master list, I would still want them to be in rows 2 and 3 in the year tab, not 2 and 6. Is this possible?

  7. #7
    Registered User
    Join Date
    03-25-2013
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: If Function is False continue to check row below for true value

    How can I get the formula to populate these with no space in between?
    Attached Images Attached Images

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,901

    Re: If Function is False continue to check row below for true value

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

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

    View Pic
    Ben Van Johnson

  9. #9
    Registered User
    Join Date
    03-25-2013
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: If Function is False continue to check row below for true value

    Here is the sample workbook with the necessary tabs and columns. The formulas from above are entered into the sheet, but I would like to get the names to populate without empty cells in between is possible. Thanks for the help.
    Attached Files Attached Files

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If Function is False continue to check row below for true value

    Try this...

    On sheet Year:

    E1 = header = Count of Records

    Enter this formula in F1:

    =COUNTIF('Renter Info'!C:C,"Year")

    Enter this array formula** in A2:

    =IF(ROWS(A$2:A2)>F$1,"",INDEX('Renter Info'!A:A,SMALL(IF('Renter Info'!C$2:C$201="Year",ROW('Renter Info'!C$2:C$201)),ROWS(A$2:A2))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Enter this formula in B2:

    =IF(A2="","",VLOOKUP(A2,'Renter Info'!A$2:E$201,4,0))

    Enter this formula in C2:

    =IF(A2="","",VLOOKUP(A2,'Renter Info'!A$2:E$201,5,0))

    Select A2:C2 and copy down until you get blanks.

    Do the same thing for each of the other sheets changing the criteria as needed.

  11. #11
    Registered User
    Join Date
    03-25-2013
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: If Function is False continue to check row below for true value

    Thank you very much! Worked perfectly

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If Function is False continue to check row below for true value

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1