easy qustion for excel experts

[Digital2k]

I'm creating a checking account balance sheet and I want to display the
balance which would be the last column (F), and last row that the data was
entered.
I will then like to copy the cell with the ending balance and have it shown
in the header(A2). Is this possible when the cell with the new balance will
always change?
Thanks,

Digital2k

 

[JE McGimpsey]

One way:

A2: =INDEX(F:F,MATCH(9.99999999999999E+307,F:F,TRUE))

 

[VBA Noob]

Hi,

This website should help. It has some code which gives you a use
defined function to return the last value.


http://j-walk.com/ss/excel/tips/tip30.htm

VBA Noo

 

[RagDyeR]

Try this in A2:

=LOOKUP(2,1/(1-ISBLANK(F1:F100)),F1:F100)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I'm creating a checking account balance sheet and I want to display the
balance which would be the last column (F), and last row that the data was
entered.
I will then like to copy the cell with the ending balance and have it shown
in the header(A2). Is this possible when the cell with the new balance will
always change?
Thanks,

Digital2k

 

[Digital2k]

No luck so far,

 

[Dave Peterson]

Post what you tried.

No luck so far,

 

[Gord Dibben]

You mean you cannot get any of the three suggestions given to work?

What have you tried and what do you get/not get that is not satisfactory?


Gord Dibben MS Excel MVP

 

[Digital2k]

OK,
I think I know why this was not working. There is a formula in the last
column(F). I'm sorry I didn't say this before.
=IF(D5>0.01,D5+F4,0)+IF(E5>0.01,F4-E5,)
So when I use the formulas given, I get 0 because the last cell is 0.
Here's how the sheet is laid out:
Column (B) is the description, Column (C) is the date, Column (D) is the
deposit, Column (E) is the withdrawal, & column (F) has the formula above to
get the balance. I want to get the last entry and show it in cell B2.
Is there a better way to do this?

 

[David McRitchie]

Take look at http://www.mvps.org/dmcritchie/excel/insrtrow.htm
the example and macros can specifically be applied to checkbook
balance.

 

[Dave Peterson]

Are you trying to get the last non-zero in column F.

I changed RagDyer's suggestion to:

=LOOKUP(2,1/(F1:F100<>0),F1:F100)

But I don't really know what you're trying to do.