Referencing cells last entered

Paulasirius · Feb 24th, 2002, 07:23 AM

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?

Aladin Akyurek · Feb 24th, 2002, 07:34 AM

Quote:

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 ]

Paulasirius · Feb 24th, 2002, 09:02 AM

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.

Aladin Akyurek · Feb 24th, 2002, 09:15 AM

Quote:

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 ]

Paulasirius · Feb 24th, 2002, 10:14 AM

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.

Aladin Akyurek · Feb 24th, 2002, 10:44 AM

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

Paulasirius · Feb 25th, 2002, 08:42 AM

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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 24th, 2002, 07:23 AM	#1
Paulasirius New Member Join Date: Feb 2002 Location: Eastbourne Posts: 8	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?

Feb 24th, 2002, 09:02 AM	#3
Paulasirius New Member Join Date: Feb 2002 Location: Eastbourne Posts: 8	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.

Feb 24th, 2002, 10:14 AM	#5
Paulasirius New Member Join Date: Feb 2002 Location: Eastbourne Posts: 8	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.

Feb 24th, 2002, 10:44 AM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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

Feb 25th, 2002, 08:42 AM	#7
Paulasirius New Member Join Date: Feb 2002 Location: Eastbourne Posts: 8	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!