how to find last cell in column with data
 

I have a very simple checkbook register. I would like to be able to
display
the ending balance in a specific cell at the top of the sheet. How can
I do this?

Thanks

Let's say your balance is in column A from A2 to somewhere below.
Put in A1

=LOOKUP(2,1/(A2:A653560),A2:A65356)

(suggested by Sandy Mann)
--
Gary's Student


" wrote:

I have a very simple checkbook register. I would like to be able to
display
the ending balance in a specific cell at the top of the sheet. How can
I do this?

Thanks

Gary's Student" wrote in message
...

=LOOKUP(2,1/(A2:A653560),A2:A65356)

(suggested by Sandy Mann)

Really? Stolen by Sandy Mann maybe <g

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"

My balance column (E) is using the formula:
(for cell E5): =IF(A5="","",(E4+C5-D5))

which always shows blank unless there is data (date actually) in column
A. Since this formula is used farther down the sheet then actual data,
the results of your suggestion are the contents of the last cell with
the forumla. (which is exactly what I asked for - not necessarily what
I wanted :( )

So let me try again in case I am heading down the wrong path. I would
like to have a running balance using a similar formula above and only
have data visible in the column if there is data in the A column. I
also want to be able to get the last cell with a balance. How can I
achive this?

Thanks again for your help.

Try:

=LOOKUP(2,1/(A2:A65356<""),A2:A65356)

but note that this will also return any text that is further down than the
last date.
--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


wrote in message
oups.com...
My balance column (E) is using the formula:
(for cell E5): =IF(A5="","",(E4+C5-D5))

which always shows blank unless there is data (date actually) in column
A. Since this formula is used farther down the sheet then actual data,
the results of your suggestion are the contents of the last cell with
the forumla. (which is exactly what I asked for - not necessarily what
I wanted :( )

So let me try again in case I am heading down the wrong path. I would
like to have a running balance using a similar formula above and only
have data visible in the column if there is data in the A column. I
also want to be able to get the last cell with a balance. How can I
achive this?

Thanks again for your help.

mmm.....

Both formulas work for me when the range is corrected (and I took the
trouble to actually read what the OP said)

=LOOKUP(2,1/(A2:A65356<""),E2:E65356)


=LOOKUP(2,1/(A2:A653560),E2:E65356)
--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Try:

=LOOKUP(2,1/(A2:A65356<""),A2:A65356)

but note that this will also return any text that is further down than the
last date.
--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


wrote in message
oups.com...
My balance column (E) is using the formula:
(for cell E5): =IF(A5="","",(E4+C5-D5))

which always shows blank unless there is data (date actually) in column
A. Since this formula is used farther down the sheet then actual data,
the results of your suggestion are the contents of the last cell with
the forumla. (which is exactly what I asked for - not necessarily what
I wanted :( )

So let me try again in case I am heading down the wrong path. I would
like to have a running balance using a similar formula above and only
have data visible in the column if there is data in the A column. I
also want to be able to get the last cell with a balance. How can I
achive this?

Thanks again for your help.