Display Results of Last Non-Empty Date Cell Across Worksheets

[JaeBon]

I have an Excel 2007 budget workbook with a worksheet for each month. Each subsequent worksheet shows date last item was paid. Column headings on each worksheet are: Transaction Date, Description, Amount, Last Paid. Some items are paid monthly, but some are paid quarterly, etc. I need a formula that will find the last non-empty date cell and display that date on the current worksheet. For example, I need the June worksheet to display that an item was paid in February if that was last time item was paid. I apologize if I have posted in wrong area.

 

[SkipVought]

Hi,

"I have an Excel 2007 budget workbook with a worksheet for each month."

THAT is your problem!

If you had one table instead on one for each month, it would be a simple AutoFilter result, if I understand your requirement correctly. Otherwise, you'd probably need a macro.

Faq68-5184

It would be helpful to upload a representative sample of your workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

In addition to addressing my previous post, would you answer this question as well: Do you mean that for each Description you want the latest Last Paid date?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JaeBon]

The Transaction Date is the date that the current payment is made. Column A
The Description is the payee who is to receive the payment. Column B
The Amount is the amount of the payment. Column C
The Last Paid is the date of the last payment to that payee. Column D

The workbook contains worksheets labeled Jan, Feb, Mar, etc.

On the Feb worksheet the formula for the Last Paid column uses column A on the Jan worksheet. That works fine.


On the Mar worksheet, if no payment was made in February, I need a formula that will go back and pick up the amount last paid which would be on the Jan worksheet. I am definitely not an expert, so I may not have set up my worksheet in the best way. If there is a way to do it, I would appreciate knowing; if not, I can start over. Thanks for taking time to answer.

 

[SkipVought]

so I may not have set up my worksheet in the best way"

I already explained why you did not set up your workbook in the best way. You have shot yourself in the foot by segmenting your data into separate sheets.

BTW, what happens in the Jan sheet for looking back to payments in the previous year?

"If there is a way to do it, I would appreciate knowing"

ONE TABLE!

However, to do it using spreadsheet features, rather than code you must do these things:

1) Put your data into ONE TABLE starting in A1
2) Sort your data by transaction data Newest to oldest
3) Convert your table to a Structured Table via Insert > Tables > Table...

Result:
[pre]
Transaction Date Description Amount Last Paid
2/7/2017 hat 33 1/5/2017
1/17/2017 shoe 44 12/22/2016
1/5/2017 hat 22
12/22/2016 shoe 33

[/pre]
The formula:
[tt]
D2: =IFERROR(
INDEX(INDIRECT("A"&ROW()+1&":$A$"&COUNT([Transaction Date])+2),
MATCH([@Description],INDIRECT("B"&ROW()+1&":$B$"&COUNT([Transaction Date])+2),0),1),"")
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JaeBon]

I really appreciate all the time and effort you have taken to try to help me. I think my best bet is to just start over. Have a good rest of the weekend.

 

[SkipVought]

Oh, I'm enjoying my weekend and the challenge you posted. I'm going to try a VBA solution as well.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Okay, here's a VBA solution,using your workbook structure (multiple sheets)
and assuming 1) that the Transaction Date column is sorted oldest to newest (ascending) and 2) that your sheets are all ordered by calendar month starting with the earliest month, copy this UDF and paste into a standard module. Use as you would any spreadsheet formula...

Function LastPD(rDescr As Range, rTranDte As Range) As Date
'SkipVought 2017 APR 22
'use as you would a spreadsheet forumla
'the two arguments are [b]Description[/b] and [b]Transaction Date[/b]
    Dim i As Integer, rFound As Range, iColDescr As Integer, iColTranDte As Integer
    
    iColDescr = rDescr.Column
    iColTranDte = rTranDte.Column
    
    For i = ActiveSheet.Index To 1 Step -1
        With Sheets(i)
            Select Case i
                Case ActiveSheet.Index
                    Set rFound = .Range(.Cells(1, iColDescr), rDescr.Offset(-1)).Find(What:=rDescr.Value, SearchDirection:=xlPrevious)
                Case Else
                    Set rFound = .Columns(iColDescr).Find(What:=rDescr.Value, SearchDirection:=xlPrevious)
            End Select
            
            If Not rFound Is Nothing Then
                LastPD = Intersect(rFound.EntireRow, .Columns(iColTranDte)).Value
                Exit For
            End If
        End With
    Next
End Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]