OFFSET formula to ignore cell with a blank formula

  • Hi,


    I have the following formula =OFFSET(Input!$B$8,1,0,COUNTA(Input!$B:$B)-1,5), column B has a formula which returns a blank based on another cell. I am trying to offset the range so it does not include the blank formula rows.


    Thanks

  • Re: OFFSET formula to ignore cell with a blank formula


    You could do this:


    COUNTA(Input!$B:$B)-COUNTBLANK(Input!$B:$B)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: OFFSET formula to ignore cell with a blank formula


    Hi Ali,


    This did not work, I am trying to create a dynamic named range, my first row of data is from B9:F9, I would like to select the range until there is a blank return on the formula in column B.

  • Re: OFFSET formula to ignore cell with a blank formula


    OK - I don't understand what you are trying to do - sorry.


    [sw]*[/sw]

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: OFFSET formula to ignore cell with a blank formula


    Hello,


    May be you could try following:


    Code
    1. =OFFSET(Input!$B$9,0,0,MATCH(TRUE, INDEX(ISBLANK(Input!$B$9:$B$500), 0, 0), 0)-1,5)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: OFFSET formula to ignore cell with a blank formula


    Hi,


    That didnt seem to work, I have attached a sample worksheet, with the named range, the named range is selecting everything until the last formula and not just the first line.


    Thanks

    Files

    • Example.xlsx

      (10.75 kB, downloaded 185 times, last: )
  • Re: OFFSET formula to ignore cell with a blank formula


    Hello again,


    Quote from Nagin;796347

    I would like to select the range until there is a blank return on the formula in column B.


    If this is your request ... believe formula in post #5 does perform as requested ...


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)