Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    last not- 'blank' row? (2k but prob all)

    I8:I13 contain formulae of the type (in I9)
    =IF(E9="","",I8-E9+F9)
    (that in I8 refers to an absolute reference elsewhere)
    What formula can I put in eg F19 which will return the value of the last not-blank cell in the range I8:I13?
    Nested IFs I suppose will do it, but are very ugly and hard to follow. And can't be extended if the range grows to >9 rows, I think.
    MAX or MIN won't do, as non-blank values won't necessarily follow a pattern.
    Thanks in advance.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: last not- 'blank' row? (2k but prob all)

    See the posts in <!post=this thread,352837>this thread<!/post>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last not- 'blank' row? (2k but prob all)

    Thanks, John, Steve's formula involving the jumbo-sized number does the trick for me.
    (Quite why and how defeats me at present!)

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: last not- 'blank' row? (2k but prob all)

    It returns the content of the highest row with something numeric:

    =VLOOKUP(65536,I:I,1)

    will do the same (until there's a version of Excel with more rows).
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last not- 'blank' row? (2k but prob all)

    Yes, I'd figured that's how it worked, and figured equally that the formula as offered contained a Pittsburgh Wind-Up.
    But thanks, still.

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last not- 'blank' row? (2k but prob all)

    With the Morefunc addin,

    =LASTROW(Range)

    Will accept whole column references.

Posting Permissions

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