identify last item in column

  • Thread starter Thread starter RickD
  • Start date Start date
R

RickD

I have a column that I add a number to each week. Say it
is column A, and A1 has the number 35. A2 gets filled in
the next week with 39. And each week I enter another
number going down column A.

I want to reference the last number in Collumn A each week
on another worksheet. How do I get excel to find the last
cell filled in in column A?
 
Hi rick,
if you have no blank rows in between try
=OFFSET('sheet1'!$A$1,COUNTA('sheet1'!$A:$A)-1,0)

Frank
 
If you do have blank rows in the column use

=LOOKUP(9.99999999999999E+307,Sheet2!A:A)

Gord Dibben Excel MVP
 
If you do have blank rows in the column use

=LOOKUP(9.99999999999999E+307,Sheet2!A:A)
Click to expand...
...

And if the last/bottommost entry could be text or number, use

=INDEX(Sheet2!A:A,MAX(MATCH({9.99999999999999E+307,"zzzzzzzzzz"},Sheet2!A:A)))
 
Hi!

If there are no numbers in the column:

=INDEX(Sheet2!A:A,MAX(MATCH({9.99999999999999E+307,"zzzzzzzzzz"},Sheet
2!A:A)))
Returns #NA!

Adapting I get:

=IF(ISNA(INDEX(Sheet2!A:A,MAX(MATCH({9.99999999999999E+307,"zzzzzzzzzz
"},Sheet2!A:A)))),LOOKUP(REPT("z",255),Sheet2!A:A),INDEX(Sheet2!A:A,MA
X(MATCH({9.99999999999999E+307,"zzzzzzzzzz"},Sheet2!A:A))))

But there's probably a better way of getting the last entry of text or
numbers with and without blanks and with and without a mix of numbers
and text.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top