Show the Last entry

[Bg]

I built a basic spreadsheet (Excel 2002) the is used for check book
balancing. There will be over 100+ rows and I would like the current balance
to always show above the "freeze" line(row 5) in cell J4. Is there a formula
to display this?

TIA
Bg

 

[Ragdyer]

This will give you the last *numeric* entry in Column A:

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

 

[Vasant Nanavati]

If your current balance is in column J, array-enter the following formula
into cell J4 (use <Ctrl> <Shift> <Enter> rather than just <Enter>):

=INDEX(J5:J65536,MAX((J5:J65536<>"")*ROW(J5:J65536))-4)

I'm sure there's a much more elegant way but this should do the job.

 

[Bg]

Thanks to all for your help. That works
Bg

 

[Guest]

Unless you are going to enter the equation to subtract (add) the output from
(input to) the account in each line. I would just copy the Balance equation
down to some high row number. Set row 5 value to equal the bottom cell. and
if you don't want to see the non-input/output balance I would use conditional
formating to make the text be invisible [ equation "=
not(or(asset<>0,debit<>0))" format ";;;"] I would then protect this column so
that I wouldn't acidentally enter something over it. Concidering how I type,
It would be fairly often.