last entry formula

B

brianwa

I'm looking for a formula that will return the last entry within a
array. ie I have a list of say A1:A20 as I fill in the array I woul
like say cell A22 show the last entry.

Thanks in advance,
B
 
J

J.E. McGimpsey

one way (assuming that there are no blanks in the middle):

A22: =INDEX(A1:A20,COUNTA(A1:A20))

If there may be blanks (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

A22: =INDEX(A1:A20,MAX((A1:A20<>"")*ROW(A1:A20)))
 
P

Peo Sjoblom

If they are filled from top to bottom

=OFFSET($A$1,COUNTA($A$1:$A$20)-1,)

if there can be blanks involved


=INDEX($A$1:$A$20,MAX(($A$1:$A$20<>"")*ROW(INDIRECT("1:20"))))

the last formula entered with ctrl + shift & enter
 
B

brianwa

I took JE's formula into another sheet and kept getting a REF error.

Here is the formula array entered.
=INDEX(Q10:Q29,MAX((Q10:Q29<>"")*ROW(Q10:Q29)))
Anyone spot anything I'm missing
 
J

J.E. McGimpsey

I don't see why you're getting a #REF! error, but I neglected to
indicate that the first term should always start in row 1:

=INDEX(Q$1:Q29,MAX((Q10:Q29<>"")*(ROW(Q10:Q29)))

since the Row() term returns the absolute row.

Better would be:

=INDEX(Q:Q,MAX((Q10:Q29<>"")*(ROW(Q10:Q29)))

or a modification of Peo's solution:

=INDEX(Q10:Q29,MAX((Q10:Q29<>"")*(ROW(INDIRECT("1:20")))))
 
B

brianwa

The first row reference explains the REF error.

I appreciate your help It works now.

Regards,
B
 
B

bill

go to edit --then go to --then special click on last cell
or --- go to function rank make the last cell the higest rank then have formula display the higest rank
 
B

bill

go to edit then

----- brianwa wrote: ----


I'm looking for a formula that will return the last entry within a
array. ie I have a list of say A1:A20 as I fill in the array I woul
like say cell A22 show the last entry

Thanks in advance
B
 
Top