Monday, April 7, 2014

yogi_Compute Row By Row For Each Category 'Week 1 Budget Remaining' Based On Data In Budget and Expenses Sheets

                                         Google Spreadsheet   Post  #1591
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Apr-07-2014
post by Stephen J. Simon: (!category-topic/docs/spreadsheets/n1cuPqAOHyo)
Tracking multiple categories across sheets
I am modifying a Budget template I found for Google Sheets by adding an Expense tab. The monthly budget is divided into weeks, so I created a tab to track expenses by category. I want to show in the Expense tab my remaining money for each category each week and remaining money for the month. See below:

Budget by week:

Expense sheet:
Note the formula I tried, and obviously failed with. Cells H6 and under have a different formula I was trying to change to the one in H5
OK, I thought I was being clear but I guess it only made sense in my head, lol
I'll just start at the beginning so you understand what I want to accomplish. In the Budget sheet, you can see the categories broke down into weekly pay checks. Each bill has a budgeted weekly amount to add up to the budgeted amount for the month. Example, Mortgage/rent is 570 per month, 142.5 set aside each week from each pay check.
I want to keep track of these weekly expenses on the Expense sheet. I have it set up already to calculate how much is spent weekly in each category, this total shows up in cells G5 thru G34 next to the corresponding categories.
My intent is to be able to list the expenses, select the category and week that they apply to, and then have the total amount spent and remaining amount for the week show up in the corresponding cells.
$142.50 typed into cell B4, category Mortgage/rent selected in Cell D4, Week 1 selected in Cell E4. Cell G5 shows $142.50, Cell H5 Shows $0.00 Cells I5, K5, M5 all show $142.50, Cell P5 shows $427.50. This would happen in all corresponding Cells in the respective columns.
The data for columns H, J, L, and M are pulled from the corresponding weekly amounts on the Budget sheet.
Does this make sense? Is it even possible? It's beyond my skill level.

Following is a solution based on my best understanding of what Stephen is trying to accomplish