PDA

View Full Version : Have Excel identify last entry in a range



aaghd
04-22-2008, 09:18 AM
I have a checkbook registry type of spreadsheet, it includes a running balance column. I need Excel to look in that range (RunningBalance) and find the last entry posted; after it finds it I need it to put it in another cell, i.e., Cell H2 would equal the last value in the range RunningBalance. then I could take H2 on all the sheets and total them on the Summary Sheet.

My goal is to have a different registry sheet for each pot of money and on a summary sheet sum the current balance on each sheet. Of course the problem is that as you enter transactions on each sheet the cell with the current balance moves as well.

Excel has to have the capability of doing this! I just don't know how.

Thanks in advance for your help.

aaghd

Bob Phillips
04-22-2008, 10:10 AM
H2: =LOOKUP(99^99,A:A)

tstav
04-22-2008, 10:13 AM
Can this help you get it going? This is psuedocode (it won't run by itself)
You place everything in the ThisWorkbook code module

Option Explicit
Private Sub Workbook_Open()
For every Sheet In Worksheets
If Sheet name <> "Summary" Then
("H2").Value = Sheet.Cells(Sheet.Rows.Count, 1).End(xlUp).Value
Total = Total + ("H2").Value
End If
Next
Worksheets("Summary").("D1").Value = Total 'Change D1 with the cell you want to show the total
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 1 Then 'Place here the column of your data
Sheet.("H2").Value = Sheet.Cells(Sheet.Rows.Count, 1).End(xlUp).Value' change 1 with the column of your data
For every Sheet In Worksheets
Total = Total + Sheet.("H2").Value
Next
Worksheets("Summary").("D1").Value = Total 'Change D1 with the cell you want to show the total
End If
End Sub

aaghd
04-22-2008, 12:44 PM
H2: =LOOKUP(99^99,A:A)


:thumb
from
KIS

aaghd
04-22-2008, 12:46 PM
Thanks for the code. I am sure that I will find it useful. Code is soooo cool!

Have a great evening,

KIS