+ Reply to Thread
Results 1 to 12 of 12

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

  1. 03-25-2013, 10:25 PM #1
    BearsBikes
    BearsBikes is offline
    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?
    Reply With QuoteRegister To Reply
  2. 03-25-2013, 10:30 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  3. 03-25-2013, 10:49 PM #3
    BearsBikes
    BearsBikes is offline
    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.
    Reply With QuoteRegister To Reply
  4. 03-25-2013, 10:54 PM #4
    BearsBikes
    BearsBikes is offline
    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.
    Reply With QuoteRegister To Reply
  5. 03-25-2013, 11:08 PM #5
    BearsBikes
    BearsBikes is offline
    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),"")
    Reply With QuoteRegister To Reply
  6. 03-25-2013, 11:21 PM #6
    BearsBikes
    BearsBikes is offline
    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?
    Reply With QuoteRegister To Reply
  7. 03-25-2013, 11:22 PM #7
    BearsBikes
    BearsBikes is offline
    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
    Reply With QuoteRegister To Reply
  8. 03-25-2013, 11:23 PM #8
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,594

    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
    Reply With QuoteRegister To Reply
  9. 03-25-2013, 11:31 PM #9
    BearsBikes
    BearsBikes is offline
    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
    Reply With QuoteRegister To Reply
  10. 03-26-2013, 09:26 AM #10
    Tony Valko
    Tony Valko is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  11. 03-26-2013, 10:52 AM #11
    BearsBikes
    BearsBikes is offline
    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
    Reply With QuoteRegister To Reply
  12. 03-26-2013, 10:02 PM #12
    Tony Valko
    Tony Valko is offline
    Forum Expert 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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1