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

 

[OaklandJim]

I can't promise that I can do this but I'll try.

Pictures are not very helpful. If the data is not confidential hopefully you could post a link to your workbook. Put the file on Dropbox, Box, 1Drive, Google Drive etc. Use the link icon above the message area.

Or, consider sharing some data (both worksheets) using Mr Excel's excellent XL2BB addin that enables you to post a portion of a worksheet. See XL2BB - Excel Range to BBCode for details.

The worst case is that you have a custom function (called a User Defined Function or UDF) built using VBA that does what is needed, if worksheet formulas do not work.

 

[OaklandJim]

Might you do a mockup of what the results should look like?

 

[OaklandJim]

How does this sound? For income categories, the Monthly Report includes all categories. When the values are calculated the categories whose value is zero are hidden. Hiding can be accomplished easily manually. You just control-click on row headers whose row has a value of zero. Of course super basic VBA code could do the hide. The same for expenses. Even though there are a lot more expense categories hiding can be accomplished in just a minute or two manually.

If you want VBA I can provide that. I'd create code that hides categories with zero value and that would unhide all rows.

 

[OaklandJim]

BTW, no mockup needed.

 

[mikemathis]

How does this sound? For income categories, the Monthly Report includes all categories. When the values are calculated the categories whose value is zero are hidden. Hiding can be accomplished easily manually. You just control-click on row headers whose row has a value of zero. Of course super basic VBA code could do the hide. The same for expenses. Even though there are a lot more expense categories hiding can be accomplished in just a minute or two manually.

If you want VBA I can provide that. I'd create code that hides categories with zero value and that would unhide all rows.

It's certainly worth a try and I'm happy to 'use' VBA code, just have no idea how to write it. Thanks for your efforts.

 

[OaklandJim]

How important is it to have empty lines between categories in expenses?

 

[OaklandJim]

..because that is giving too much grief. I have a workbook that does what you want but I've not found a way to add those empty rows for expenses in the Monthly_Report.

 

[mikemathis]

..because that is giving too much grief. I have a workbook that does what you want but I've not found a way to add those empty rows for expenses in the Monthly_Report.

If it's too much grief, not needed at all. No worries about 'empty rows'. The data is 99.9% of the battle.

 

[OaklandJim]

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