Code/Rules for pulling cells between tabs

Hello All,

I wanted to thank you ahead of time for any time spent on helping me.
I am looking to create logic so no matter who uses the document certain rules will stay in place. Referencing the attached file there there are two tabs; Overview and Budget. I am looking to use a formula in the Overview tab in Columns A, B, C and D below row 14 which will look for new categories and totals that are added to the Budget tab.
For example: if a user was to add category 7 to the budget tab there would be a formula in row 20 column A in the Overview tab which would bring the category name over and then formulas in row 20 columns B, C, and D which would take the totals from Category 7 as well.
I tried using code to recognize bold characters and bring over the bold Categories but was unable to figure out how to get Row 15 to recognize that Category 1 had already been used in row 14 and to move on to the next for row 15.

Public Function IsBold(c As Range) As Boolean
On Error GoTo Handler
IsBold = c.Font.Bold
Exit Function
Handler:
If Err.Number = 94 Then IsBold = True
End Function

Since I wasn't able to make it past pulling over the category tab I didn't even attempt to try and get columns B, C and D to only pull totals from the Category listed in column A.
Any help would be greatly appreciated.

Welcome to the forum!

I think you're making life hard on yourself by trying to use bold as an identifier of categories. If you use a helper column, I believe you can do this with formulas alone. In my attachment, I've turned column Q on "Budget" into a helper column. I use the following formula, which must be array-entered (using Ctrl + Shift + Enter instead of Enter) in Q7, then filled down:

=IF(AND($A7<>"",ISBLANK($B7:$N7)),"Category",IF($A7="Total",$A7,IF(AND(ISBLANK($K7:$N7)=FALSE,ISBLANK($B7:$J7)),"Subtotal","")))

This formula should label your category, total, and subtotal rows so that they can be easily identified in the next part of my solution. I've left the helper column visible in the attachment, but you can easily hide it to keep it out of view.

Using the helper column, you can use the following formula, which must also be array-entered, in the Overview sheet, cell A14:

=IFERROR(INDEX(Budget!$A$7:$A$100,SMALL(IF(Budget!$Q$7:$Q$100="Category",ROW(Budget!$Q$7:$Q$100)),ROW(1:1))-6),"")

Fill it down beyond what you need and it should auto-populate with all of your categories. It will also automatically include any changes or new categories that are added to the budget sheet. You can then use the formula below (array-entered again) in B14:

=IFERROR(INDEX(Budget!K$7:K$100,SMALL(IF(Budget!$Q$7:$Q$100="Subtotal",ROW(Budget!$Q$7:$Q$100)),ROW(1:1))-6),"")

Fill it right through D14 and down and it will return the subtotals for each category.

You can modify the formula to provide your "spending to date" numbers as well in column F. With everything in place, you should not have to touch the Overview sheet anymore. It should automatically reflect any changes to the budget sheet.

My formulas are currently set up to cover through row 100 of the Budget sheet, but you can increase that number if you expect more rows to be used. Just make sure you increase all of them so the sizes of the arrays still match. Take a look at the attachment and see if it'll do the trick:

Hello cuttothechase,

Welcome to the forum!

Here is the VBA code to copy the categories automatically. The macro is called whenever the "Overview" worksheet becomes the active sheet. All the categories are updated when this occurs.

The attached workbook has the macros added.

Overview Event Code

Private Sub Worksheet_Activate()
    Call CopyCategories
End Sub

Copy Categories Macro Code

Sub CopyCategories()

    Dim CatCell As Range
    Dim DstRng  As Range
    Dim DstWks  As Worksheet
    Dim SrcRng  As Range
    Dim SrcWks  As Worksheet
    Dim Start   As String
    Dim Total   As Range
    
        Set DstWks = Worksheets("Overview")
        Set SrcWks = Worksheets("Budget")
        
        Set DstRng = DstWks.Range("A14")
        Set SrcRng = SrcWks.Range("A6").CurrentRegion
        
        On Error GoTo ErrorHandler
        
            Set CatCell = SrcRng.Columns(1).Find("Category", , xlValues, xlPart, xlByRows, xlNext, False, False, False)
            If CatCell Is Nothing Then Exit Sub
            
            Application.ScreenUpdating = False
            
                Start = CatCell.Address
            
                Do
                    Set Total = CatCell.End(xlDown).Offset(1, 10)
                
                    With DstRng
                        .Cells(1, 1).Formula = CatCell.Value
                        .Cells(1, 2).Formula = "=" & "'" & SrcWks.Name & "'!" & Total.Offset(0, 0).Address(False, False)
                        .Cells(1, 3).Formula = "=" & "'" & SrcWks.Name & "'!" & Total.Offset(0, 1).Address(False, False)
                        .Cells(1, 4).Formula = "=" & "'" & SrcWks.Name & "'!" & Total.Offset(0, 2).Address(False, False)
                        .Cells(1, 5).Formula = ""
                        .Cells(1, 6).Formula = "=" & "'" & SrcWks.Name & "'!" & Total.Offset(0, 3).Address(False, False)
                    End With
                
                    Set DstRng = DstRng.Offset(1, 0)
                
                    Set CatCell = SrcRng.FindNext(CatCell)
                    If CatCell.Address = Start Then Exit Do
                Loop
            
ErrorHandler:
            Application.ScreenUpdating = True
            
            If Err <> 0 Then
                MsgBox "Run-time error '" & Err.Number & "':" & vbLf & vbLf & Err.Description
                On Error GoTo 0
            End If
            
End Sub

Glad to help, good luck!

The helper column is there, so you might as well use it for your sums, too. I would change your SUM formula in K53 to the following:

=SUMIF($Q$7:$Q52,"Subtotal",K$7:K52)

This formula should sum the numbers above that fall into rows identified as "Subtotal" rows. It should respond better to the insertion of new rows. You can fill right for the other totals.