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

Thread: Referencing cells last entered

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Eastbourne
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a financial spreadsheet in Microsoft Excel 97 which contains several worksheets. I am trying to create a formula on a summaries worksheet that will locate the last entry in a payments column on another worksheet containing dates, items and payments. There seems to be no function for referencing a cell that is the last one entered in a column. If I can find a way to do this, I can then use a lookup reference function to copy the date into the summaries worksheet as well. Does anyone know what is the usual way of dealing with this type of referencing?

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

    Default

    On 2002-02-24 06:23, Paulasirius wrote:
    I have a financial spreadsheet in Microsoft Excel 97 which contains several worksheets. I am trying to create a formula on a summaries worksheet that will locate the last entry in a payments column on another worksheet containing dates, items and payments. There seems to be no function for referencing a cell that is the last one entered in a column. If I can find a way to do this, I can then use a lookup reference function to copy the date into the summaries worksheet as well. Does anyone know what is the usual way of dealing with this type of referencing?
    Lets say that Sheet1!A:A houses your dates:

    =MATCH(9.99999999999999E+307,Sheet1!A:A)

    will give you the row number of the last entry. You could equally use the payments column instead of the dates column.


    [ This Message was edited by: Aladin Akyurek on 2002-02-24 06:34 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Eastbourne
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thankyou for that idea. However, it is only necesary to use a number greater than the number of rows you are using in the column. Your 9.9999999999.....E+307 is a number which is 307 digits long, and that is considerably greater than the maximum 65536 rows in any excel sheet. My list will never contain any more than 500 rows, and probably will only ever have 200 in it. So the formula INDEX(Sheet1!F:F,MATCH(500,Sheet1!F:F)) will find the last cell in column F containing data, and the INDEX function returns the value of the found cell. There are certain limitations, but the excel help files explain that well enough.
    Thankyou once again.

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

    Default

    On 2002-02-24 08:02, Paulasirius wrote:
    Thankyou for that idea. However, it is only necesary to use a number greater than the number of rows you are using in the column. Your 9.9999999999.....E+307 is a number which is 307 digits long, and that is considerably greater than the maximum 65536 rows in any excel sheet. My list will never contain any more than 500 rows, and probably will only ever have 200 in it. So the formula INDEX(Sheet1!F:F,MATCH(500,Sheet1!F:F)) will find the last cell in column F containing data, and the INDEX function returns the value of the found cell. There are certain limitations, but the excel help files explain that well enough.
    Thankyou once again.
    I'd sugguest not using yours. Mine doesn't have anything to do with the available number of rows.

    Use rather:

    =INDEX(Sheet1!F:F,MATCH(9.99999999999999E+307,Sheet1!F:F))

    If F contains dates, enter as last entry in F

    18-Jun-2002 and see what you get.

    Aladin

    Addendum:

    See

    http://www.mrexcel.com/wwwboard/messages/18830.html

    for an explanation of 9.99999999999999E+307.

    [ This Message was edited by: Aladin Akyurek on 2002-02-24 08:39 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Eastbourne
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Of course, and how silly of me. Sorry about that. Even so, I cannot see why such a large 307 digit number needs to be used for practical everyday work.
    Wouldn't a number larger than any payment I'm likely to make on my credit cards suffice? Say 100 when I never make payments over 90 for instance.
    Do you know what the help files mean by saying that MATCH only works when the data is in ascending order, because my payments are not in any order yet the formula works.

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

    Default

    Paula,

    " I cannot see why such a large 307 digit number needs to be used for practical everyday work.
    Wouldn't a number larger than any payment I'm likely to make on my credit cards suffice? Say 100 when I never make payments over 90 for instance."

    Sure, as long as it's guaranteed that there never will be any number bigger than X (which is significantly smaller than the max number I mentioned. You have of course to remember that when you have another application in which you need to apply it to a dates column, for example. I'd say: stay safe. Such a big number doesn't do any harm performancewise at all.

    "Do you know what the help files mean by saying that MATCH only works when the data is in ascending order, because my payments are not in any order yet the formula works."

    That's exactly its charm: it's insensitive to order or any number of blanks in the range. It works because (match-type being TRUE/1 --which can be omitted) failing to find a number bigger than 9.9...E+307 in the range, MATCH returns the row number of the latest numeric entry it has looked at. That's this "feature" that we're exploiting. It's a useful side-effect, don't you think?

    By the way, it can be used only with columns of numeric type.

    Aladin

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Eastbourne
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thankyou, Aladin. As a female qualified programmer in Cobol and various Basic dialects, I never got a career due to my age and gender so I got left out in the cold and am now retired and managing all my computing problems alone. You and the others on this web site are a Godsend to me, now at last after three years of struggling with the help files, I will have the benefit of the experiences of real computer pros once again. What a wonderful web site!

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
  •