Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel lookup (Excel xp)

    I have a range G2:G18 that i am filling in each month starting at G2. I am now at G7 but want to write a formula to to look for the next "Blank" cell, which would be G8, and take the RIGHT 3 characters of the row above it.

    What function do i use to look through G2 through G18 and find the last blank cell and offset 1 row and then take the right 3 chars? thank you for the help.

  2. #2
    Star Lounger
    Join Date
    Sep 2003
    Location
    Betekom, Belgium
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel lookup (Excel xp)

    Hi,

    if there ar no cells further down the column then it is alwas the last cell you are going to take as start for the offset.
    lastrow = Range("g65526").End(xlUp).row: gives you the last cell it encounters in column g conting from row 65526 upwards
    I am not certain but when starting from row 1 counting downwards Blank = Range("g1").End(xldown).row+1 it should give you the first blank cell regardless other blank cell downwards the same column.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel lookup (Excel xp)

    Try the ARRAY formula (confirm with ctrl-shift-enter).

    <pre>=RIGHT(INDEX(G2:G18,MATCH(REPT("z",255),G2:G1 8,1)),3)</pre>


    This assumes that G2:G18 is all text, it will treat numbers as "blank"

    Steve

Posting Permissions

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