One cell is contingent on another - creating circular reference

[mikemathis]

I have a sheet that brings expenses for a given date range with the following formula:

=SUMPRODUCT(--ISNUMBER(FIND($B7,PNC_Checking!$E$3:$E$501))*(PNC_Checking!$B$3:$B$501>=$B$2)*(PNC_Checking!$B$3:$B$501<=$B$3),PNC_Checking!$H$3:$H$501)

Where the formula is looking for B7 (which is a category), I want to have all the categories listed by date range as well. Meaning, out of 40 categories, if only 10 have expenses for the time period, those are the only ones I want visible, (and sorted if possible) THEN to run the above formula for the amount of expenses for the same time period.

I'm guessing that VB will be an answer but I know nothing about VB and hope that there's a macro or a way to pause between two formulas - that will not result in a circular reference error.

thanks

 

[mikemathis]

Here is The workbook.

CheckingRegister.xlsm

1drv.ms

I added some range names so formulas can include those names in lieu of the range-address-based references. For example, see this formula:

=IF( rrCategory="","", SUMPRODUCT(--ISNUMBER(FIND(rrCategory,CategoriesIncomeChecks))*(DatesChecks>=DateTo)*(DatesChecks<=DateFrom),DepositsChecks))

The rrCategory name has "relative" addressing like $B7.

I added some basic VBA code to hide rows with zero value and to unhide all rows.

Option Explicit

Sub ToggleHidden()

    Dim oButton As Object

    Set oButton = ActiveSheet.Buttons(Application.Caller)

    If oButton.Caption = "Hide" _
    Then
        Call HideZeroValueRows
        oButton.Caption = "Show"
    Else
        Call ShowAllValueRows
        oButton.Caption = "Hide"
    End If

End Sub

Sub HideZeroValueRows()

    Dim iRowsProcessed As Long

    Dim iRowsInRange As Integer

    Dim rCell As Range
   
    iRowsInRange = [Monthly_Report].Range("ExpensesPeriod").Rows.Count

'   Hide income rows if the value for an expense is zero or empty.
    For Each rCell In [Monthly_Report].Range("IncomePeriod")
   
        If rCell.Value = 0 Or rCell.Value = "" _
         Then rCell.EntireRow.Hidden = True
   
    Next rCell
   
'   Hide expense rows if the for an expense value is zero or empty.
    For Each rCell In [Monthly_Report].Range("ExpensesPeriod")
   
        If rCell.Value = 0 Or rCell.Value = "" _
         Then rCell.EntireRow.Hidden = True
   
    Next rCell
  
End Sub

Sub ShowAllValueRows()

    Dim rCell As Range
   
    With [Monthly_Report]
       
        .Range("IncomePeriod").EntireRow.Hidden = False
   
        .Range("ExpensesPeriod").EntireRow.Hidden = False
   
    End With

End Sub

Jim, I will load this later on and report back. I'm excited for the results...Thanks so much
Mike

 

[mikemathis]

Jim, I will load this later on and report back. I'm excited for the results...Thanks so much
Mike

Works a champ. It really is a bit of magic. Just to make myself a little smarter, how/where would i be able to see your VBA? Is it on the sheet level or over the entire workbook?
Thanks so much.

 

[OaklandJim]

You'll need to add the Developer Tab to the ribbon menu.

• Click the File menu and select Options.
• Click Customize Ribbon.
• Select Main Tabs from the "Customize the ribbon" drop-down menu.[1]
• Check the box next to Developer and click OK.

Go back to the worksheet. The the Developer Tab should now be visible on the ribbon. Click on that then click on Visual Basic ribbon item on the right.