Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: cell value in last row of column

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is driving me nuts .. I have a 2-column set of numbers cols (G-H)and the number
    of rows in the column is varied by a user macro - so I know how many rows are in the column

    I want cell C10 to always display the value of the last row in the column using formula (not VBA)

    I know this is simple .. but its driving me mad .. what is the formula to get the contents of a cell in column H row X - where x = the number of rows the user has chosen

    thanks
    Bill

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok .. I worked it out .. I have the macro store the calculated last cell in the worksheet, eg C10 and then the formula
    =Indirect(C10) can go and get the value that I need.

    but ..how can I use variables within the "indirect" formula rather than a hard coded cell reference ?
    Thanks Bill

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,060
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default



    In C10 enter:

    =MATCH(9.99999999999999E+307,G:G)


  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Used a named formula which adjusts dynamically and you won't need to worry about the indirect referencing.

    Insert>Name>Define
    e.g "Last_Cell_in_G"

    Refers to:
    =OFFSET(Sheet1!$G$1,MATCH(9.9E+307,Sheet1!$G:$G,1)-1,0,1,1)

    This will find the last numeric value in the column.

    Change the RefersTo formula to
    =OFFSET(Sheet1!$G$1,COUNTA(Sheet1!$G:$G)-1,0,1,1)

    and you will get the last cell value (text or numeric). This requires that there is a continuous range (no gaps in the column).

    You can shorten the name to make it easier (CLast, for example).

    HTH,
    Jay

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hey .. it works great ..
    thanks
    Bill

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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