Select worksheet name in formula

[mikemathis]

I have a 'poor mans' Quicken with each year in a separate tab (2020, 2021, 2022, 2023, ...) Within each tab is a listing of expenses for the year. So far, so good. I also have a "Report" tab where I consolidate the various categories into a report. Here's the formula I'm currently using

=SUMPRODUCT(--ISNUMBER(FIND($B19,'2023'!$F$2:$F$782))*('2023'!$A$2:$A$782>=$C$1)*('2023'!$A$2:$A$782<=$C$2),'2023'!$C$2:$C$782)

Where: B19 is the string I'm looking for
'2023' is the tab name
$C$1 is the start date
$C$2 is the end date

What I'd like to do is to have (on the Report page, a cell where I select the year (tab name) and the formula would work. Each tab has identical columns so that's no issue.

Thanks in advance for your solutions.
Mike

 

[rlv01]

Why not instead put one formula in each year tab:

=SUMPRODUCT(--ISNUMBER(FIND('Report'!$B19,$F$2:$F$782))*($A$2:$A$782>='Report'!$C$1)*($A$2:$A$782<='Report'!$C$2),$C$2:$C$782)

Then use the INDIRECT and ADDRESS functions to pull the value into the Report sheet based on year selection

INDIRECT(ADDRESS(1,2,,,A1))

where the year is in A1 in the above example.

You could also do it in VBA via the Worksheet_Change in the 'Report' worksheet. Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim S As String, WS As Worksheet
    If Target.Address = "$A$1" Then
        On Error Resume Next
        Set WS = ThisWorkbook.Worksheets(CStr(Target.Value))
        On Error GoTo 0
        If Not WS Is Nothing Then
            S = "=SUMPRODUCT(--ISNUMBER(FIND($B19,'2023'!$F$2:$F$782))*('2023'!$A$2:$A$782>=$C$1)*('2023'!$A$2:$A$782<=$C$2),'2023'!$C$2:$C$782)"
            S = Replace(S, "2023", Target.Value)
        Else
            S = "n/a"
        End If
        Application.EnableEvents = False
        Target.Offset(0, 1).Formula = S
        Application.EnableEvents = True
    End If
End Sub

The above example assumes that the year is in cell A1 and the formula in cell B1

 

[mikemathis]

I'm working today but will try out this solution. thank you so much for the 'work'!