Refer to last entry in a column

[kelly1]

Is it possible to refer to the last entry in a column? So when I add entries it looks at the new balance.

e.g. I need the result of the last/bottom entry of Sheet 2, column D.

Thanks for any help

Kelly

[HalfAce]

Hi Kelly,
If you’re looking for a formula, you could go down to a cell below where you expect your range to be (say D1001) and enter =SUM(D1:D1000) . Then enter in the cell you want your new balance in: =D1001 . (If this cell is on a sheet other than sheet2, then use: =Sheet2!D1001 ).

If you’re looking for a VBA approach you could use this:
Sheets(“Sheet2”).Cells(.Cells(65536, 4).End(xlUp).Value or
Sheets(“Sheet2”).Cells(Rows.Count, 4).End(xlUp).Value

This assumes (from what I gathered in your post) that you are totaling up the entries in Col.D (?)
I’m sure there are other ways, but if this is what you're asking, one of these should do it.
This help?
HalfAce

[kelly1]

Thanks for the reply but I don’t need a total of the entire column, because each row has a debit/credit column so Column D is a new balance for each row.

So it’s just the last entry in the column.

Kelly

[HalfAce]

Ok Kelly try this.
In the sheet module for sheet2 paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
[A1].Value = Cells(Rows.Count, 4).End(xlUp).Value
End If

End Sub

Now change the reference [A1] to the cell you want to see the last entry in Col.D and give it a try.

This better?
HalfAce

[Aladin Akyurek]

Originally Posted by kelly1

...I need the result of the last/bottom entry of Sheet 2, column D...

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

[kelly1]

Hi HA and thanks for the code.

I right clicked the sheet tab and pasted in the code, changed the A1 to H1 and nothing is happening.

What am I doing wrong?


Kelly

[HalfAce]

Aladin,
I knew there must be an actual formula and racked my brains to think of one. I never would've been able to come up with this though!
Hope you don't mind if I save it in my "bag of tricks".

Thanks,
Dan

[HalfAce]

Kelly,
Turn on your Controls Toolbox toolbar and see if the design mode is turned on. (If so turn it off.) Other than that I don't know what would be stopping it from working.
If that don’t fix it, use Aladin’s formula. That works great!
HalfAce

[Aladin Akyurek]

Originally Posted by HalfAce

Aladin,
I knew there must be an actual formula and racked my brains to think of one. I never would've been able to come up with this though!
Hope you don't mind if I save it in my "bag of tricks".

Thanks,
Dan

No, go ahead.