Last non-blank cell

  • Thread starter Thread starter Peter Twydell
  • Start date Start date
P

Peter Twydell

I have a spreadsheet which consists of a list of invoices which grows as
the year progresses. The invoice date is in column A.
What I want to do is a calculation based on the date in the last
non-blank cell in column A. How can I refer to this cell in a formula?
 
If you are looking for a number, just look for any number larger than
possible in your list.

=MATCH(99999999,A:A)
 
To expand on what Don has offered, if you wanted to SUM everything fro
A2 to the last cell in Col A with a numerical value, it would loo
something like this ...

=SUM(A2:INDEX($A:$A,MATCH(9.999999999E+307,$A:$A)))

To make things a little shorter/easier, you can define a named rang
(Insert -> Name -> Define) and as your formula type in ...

=INDEX(Sheet3!$A:$A,MATCH(9.99999999E+307,Sheet3!$A:$A))

(or whatever sheet your using)
Make a name like bigN or something, then your formula would be ...

=SUM(A2:bigN
 
Don Guillett said:
If you are looking for a number, just look for any number larger than
possible in your list.

=MATCH(99999999,A:A)
Click to expand...
Thank you for the prompt response, but that's not what I need. I want
the last date in a variable-length list of dates.

BTW, my posting showed up in your follow-up as part of the sig. Not a
good thing in a thread.
 
I thought you would be able to figure it out from there.
=INDEX(A:A,MATCH(99999999,A:A))
to get the actual date
Can't speak to the placement of your posting. I TOP posted, as usual.
 
Don Guillett said:
I thought you would be able to figure it out from there.
=INDEX(A:A,MATCH(99999999,A:A))
to get the actual date
Can't speak to the placement of your posting. I TOP posted, as usual.
Click to expand...
That just returns zero. The original formula you supplied returned a
date of 05 June 1979, which is over 35 years before what I need.


Your last posting looked like this (between the asterisks):

**********
I thought you would be able to figure it out from there.
=INDEX(A:A,MATCH(99999999,A:A))
to get the actual date
Can't speak to the placement of your posting. I TOP posted, as usual.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Peter Twydell said:
Thank you for the prompt response, but that's not what I need. I want
the last date in a variable-length list of dates.

BTW, my posting showed up in your follow-up as part of the sig. Not a
good thing in a thread.
Click to expand...
**********
Note that the "-- " before your name is the conventional sig separator.
Either your OE is seriously screwed up, or you're somehow putting your
replies in the wrong place (apart from top posting, which I wasn't going
to mention)..
 
The MS Newsgroups seem to be different than Usenet as a whole.

We pretty much top post around here.

(I personally like it. I can read the responses and decide if I want to read
the original question.)
 
If you want the last, use what I sent before. If you want the LATEST date
regardless of position in col A, then try:
=INDEX(A:A,MATCH(MAX(A:A),A:A,0))
Again, I am TOP posting as many do in these ngs.
My posting SHOULD appear at the TOP at the top of this message.
 
Don Guillett said:
If you want the last, use what I sent before. If you want the LATEST date
regardless of position in col A, then try:
=INDEX(A:A,MATCH(MAX(A:A),A:A,0))
Again, I am TOP posting as many do in these ngs.
My posting SHOULD appear at the TOP at the top of this message.
Click to expand...
I don't know what you've done differently, but your posting is now OK.
Top/bottom posting seems to be a matter of personal preference, apart
from newsreader default settings. That wasn't the problem; the message
appearing under the sig was.

And I still can't get what I want from the formula you supplied, i.e.
the date in the last non-blank cell.
 
So do you want the highest date or the last date in the column lookin
down? Is there anything besides dates in these column(s)? If thei
not working, you're not specifying something else - somethings missing
 
You may send me a SMALL workbook with details of what you want.

BTW. I didn't do anything differently.
ALL of my messages were posted in EXACTLY the same way.
I post MANY messages here every day. Ask google.
 
Peter said:
I have a spreadsheet which consists of a list of invoices which grows as
the year progresses. The invoice date is in column A.
What I want to do is a calculation based on the date in the last
non-blank cell in column A. How can I refer to this cell in a formula?
Click to expand...

One way:

Set rng = Range("A:A").Find("*", Range("A1"), _ SearchDirection:=xlPrevious)

Alan Beban
 
Don Guillett said:
You may send me a SMALL workbook with details of what you want.

BTW. I didn't do anything differently.
ALL of my messages were posted in EXACTLY the same way.
I post MANY messages here every day. Ask google.
Click to expand...
I did see that you are a frequent poster. I don't understand why things
happened the way they did. PCs take the concept of the innate hostility
of inanimate objects to a much higher plane than anything else.

My spreadsheet is simple. It is a list of invoices issued, with details
of date, customer, invoice number, etc.

For each invoice (one per row), column A contains the date. What I want
to do is find the last date in this column, i.e. the last non-blank
cell, and use it in a calculation. Only one cell, not a row or column,
will contain this calculation.
 
Peter said:
I did see that you are a frequent poster. I don't understand why things
happened the way they did. PCs take the concept of the innate hostility
of inanimate objects to a much higher plane than anything else.

My spreadsheet is simple. It is a list of invoices issued, with details
of date, customer, invoice number, etc.

For each invoice (one per row), column A contains the date. What I want
to do is find the last date in this column, i.e. the last non-blank
cell, and use it in a calculation. Only one cell, not a row or column,
will contain this calculation.
Click to expand...

I sent the following response Wednesday afternoon about 4:00; watch for
word wrap--it's all one line:
Set rng = Range("A:A").Find("*", Range("A1"), SearchDirection:=xlPrevious)

Alan Beban
 
Hey,

I wonder if this is what you're looking for...
{=INDEX($A$1:$A$500,LARGE(IF($A$1:$A$500<>"",ROW($A$1:$A$500)),1),1)}
This will put the contents of the last non-blank cell into anothe
cell.
I guess you could then refer to the contents of that cell in you
calculation.
I was looking for this the other day and someone helped me out, I hop
this helps you.

Cheers.

(By the way, don't try typing the curly brackets - when you pres
'Enter' hold 'Ctrl' and 'Shift' together)
 
What I want to do is find the last date in this column, i.e. the last
non-blank

Then, again, this simple macro should do it.
=INDEX(A:A,MATCH(99999999,A:A))
 
Bon Rouge said:
Hey,

I wonder if this is what you're looking for...
{=INDEX($A$1:$A$500,LARGE(IF($A$1:$A$500<>"",ROW($A$1:$A$500)),1),1)}
This will put the contents of the last non-blank cell into another
cell.
I guess you could then refer to the contents of that cell in your
calculation.
I was looking for this the other day and someone helped me out, I hope
this helps you.

Cheers.

(By the way, don't try typing the curly brackets - when you press
'Enter' hold 'Ctrl' and 'Shift' together).
Click to expand...

Many thanks - that's done the trick!

Is the CTRL+SHIFT+ENTER to tell Excel that this is an array formula?

Thanks BTW to everyone else who tried to help.
 
All you are doing with this array formula or my simpler formula is finding
the last cell with data. Both will fail if a non date number or letters are
the last in the column.
{=INDEX($A$1:$A$500,LARGE(IF($A$1:$A$500<>"",ROW($A$1:$A$500)),1),1)}
Click to expand...
=INDEX(A:A,MATCH(99999999,A:A))
If you MUST find the last DATE in column A, try this UDF. Place in a regular
module and use =LD()

Function LD()
Application.Volatile
For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If IsDate(Cells(i, 1)) Then
LD = Cells(i, 1)'for date use .address to find address of that date
Exit Function
End If
Next i
End Function
 
Back
Top