how to find last cell in column with data

  • Thread starter Thread starter keyser_Soze
  • Start date Start date
K

keyser_Soze

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:A65356>0),A2:A65356)

(suggested by Sandy Mann)
 
=LOOKUP(2,1/(A2:A65356>0),A2:A65356)

(suggested by Sandy Mann)
Click to expand...

Really? Stolen by Sandy Mann maybe <g>

--
HTH

Sandy
(e-mail address removed)
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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
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:A65356>0),E2:E65356)
--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
Back
Top