+ Reply to Thread
Results 1 to 16 of 16

Skip where no true

  1. 01-02-2018, 06:52 AM #1
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Skip where no true

    Hi Everyone,

    I would like to drag a formula down, skipping where the formula is not true but still maintain a continuous sequence

    See attached

    Thanks
    Kevin
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-02-2018, 06:55 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,111

    Re: Skip where no true

    In D3 copied down:

    =IFERROR(INDEX($B$3:$B$9,AGGREGATE(15,6,(ROW($B$3:$B$9)-ROW($B$3)+1)/($B$3:$B$98<>""),ROWS($B$3:B3))),"")
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 01-02-2018, 06:57 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,099

    Re: Skip where no true

    Try:

    =IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$3:$B$11)/($B$3:$B$11<>""),ROWS(D$3:D3))),"")
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  4. 01-02-2018, 07:01 AM #4
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: Skip where no true

    Thank you Ali, thank you Glenn both worked great

    Kevin
    Reply With QuoteRegister To Reply
  5. 01-02-2018, 07:02 AM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,111

    Re: Skip where no true

    You're welcome!
    Reply With QuoteRegister To Reply
  6. 01-02-2018, 07:03 AM #6
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: Skip where no true

    Not sure why but I can't add rep to AliGW I guess that means you've answered a lot of my questions recently, Thanks!!
    Reply With QuoteRegister To Reply
  7. 01-02-2018, 07:06 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,111

    Re: Skip where no true

    Don't worry about that - it stops you from rewarding me too many times on the trot, so you have to award it to others before you can reward me again. It doesn't matter to me - it's nice, but solving your problems is of greater satisfaction to me. Thanks for the thought, though!
    Reply With QuoteRegister To Reply
  8. 01-02-2018, 07:24 AM #8
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Skip where no true

    Hi

    Try this formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 01-02-2018, 09:31 AM #9
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: Skip where no true

    Hi,

    It doesn't appear to work correctly?
    Reply With QuoteRegister To Reply
  10. 01-02-2018, 09:32 AM #10
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,111

    Re: Skip where no true

    What doesn't work correctly? If not, in what way? More detail, please.
    Reply With QuoteRegister To Reply
  11. 01-02-2018, 09:54 AM #11
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: Skip where no true

    This =IFERROR(INDEX(B3:B$9,ROW()-2+(B3="")),"") offered by Jose
    It doesn't pickup 30 in the example given
    Reply With QuoteRegister To Reply
  12. 01-02-2018, 10:05 AM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,099

    Re: Skip where no true

    I'm not sure if the absence of a $ in B3:B$9 was intentional or not... but even with it, it didn't work
    Reply With QuoteRegister To Reply
  13. 01-02-2018, 10:07 AM #13
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,111

    Re: Skip where no true

    No, it doesn't work. It's to do with the section where the adjacent column is being tested for a value - the logic doesn't quite stack up, as far as I can see.
    Reply With QuoteRegister To Reply
  14. 01-02-2018, 10:54 AM #14
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Skip where no true

    Hi
    I was wrong.

    The range for counting false values is greater than the range you want to get.

    My apologies.
    Reply With QuoteRegister To Reply
  15. 01-02-2018, 11:24 AM #15
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,099

    Re: Skip where no true

    You rarely get it wrong Jose!!
    Reply With QuoteRegister To Reply
  16. 01-02-2018, 11:29 AM #16
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: Skip where no true

    Hi,

    No apology necessary I really appreciate you taking the time to help me

    Thanks
    Kevin
    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)

Similar Threads

  1. how to skip user form clear to skip particular text box
    By narsing18 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-05-2017, 08:44 AM
  2. [SOLVED] IF FALSE, SKIP TO NEXT TRUE ROW - Need formula, not filters
    By rwernlund in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-25-2015, 04:54 PM
  3. [SOLVED] Skip "False" in IF and continue numbering in cell until True match, then move down cell
    By simonfinnerty in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2015, 06:06 PM
  4. # of blank labels to skip
    By jdlc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2014, 03:36 PM
  5. Help popup window if condition is TRUE in change event if conditon is true
    By fanku in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-09-2014, 12:46 PM
  6. If statement is true, skip line go to next value
    By jperl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-24-2014, 12:24 PM
  7. Formula for avg skip, max skip, longest out
    By Jordans121 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-12-2010, 04:56 PM

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