Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Using an anomaly in LOOKUP, I can find the contents of the last non-blank cell in a column (eg Column A) using the very simple formula:

    Code:
    =LOOKUP(REPT("z",255),A:A)
    . . . or one of many variations of this method.

    How can I find the cell address of that same last non-blank cell, still using a formula ?

    I do want to to use a formula because my eventual goal is to use it as part of the definition of a dynamic range which extends to the last non-blank cell. I should say that there will be blank cells within the range, otherwise this would be easier !

    Thanks.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Onces again I posted too soon !

    This (albeit roundabout) formula works fine:

    Code:
    =ADDRESS(MATCH(LOOKUP(REPT("z",255),Sheet1!A:A),Sheet1!A:A,0),1)
    Now my Named Range automatically adjusts itself to the amount of data present in Column A, which is what I wanted.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    In passing, none of this worked within the Named Range definition without a $ sign in front of each cell reference.

    Please can you tell me the "rules" regarding the use of $ in this context ?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could use:
    =ADDRESS(LOOKUP(REPT("z",255),A:A,ROW(A:A)),1)

    If you are using this in a defined name then if you don't use $ signs, the ranges are relative to the active cell at the time you define the name.
    I assume you are aware that that will ignore any numeric cells.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    HTML Code:
    I assume you are aware that that will ignore any numeric cells.
    Yes, thanks Rory. I use another version of the "test" when the cells have numeric content.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could just use:
    =ADDRESS(LOOKUP(2,1/(A:A<>""),ROW(A:A)),1)
    which works for both.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    =ADDRESS(LOOKUP(2,1/(A:A<>""),ROW(A:A)),1) returns #NUM!

    Am trying to work out why - there's nothing special about the Worksheet (eg protection) or the data itself - just dates and amounts.

    Thanks though.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Pre 2007 you can't use the entire column in that formula. It will work with A2:A65536 for example.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thank you !

Posting Permissions

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