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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Copy Categories Macro Code

Please Login or Register  to view this content.

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.