Selected Answer
This is the formula you are looking for.
[June!B4] =SUMIF($D$30:$D$31,$B$2,$B$30:$B$31)
That is the easy part. The difficult part is to control the ranges. Frankly, I believe that will be very, very hard with the layout as you have it at present. I have three recommendations for you.
- Set up ranges to be addressed by name, not by their worksheet address, then manage the addresses using the Name Manager.
If you were to convert your Table1 to a normal range and name VARIABLES!$B$4:$B$31 as Categories then the Data Validation Source would simply be =Catagories, June!B2 could be =INDEX(Categories,19), and any changes in the names range would be automatically carried to your workbook without the need to change any formulas.
As an alternative you might keep Table1 in VARIABLES (perhaps give it a more descriptive name) and refer to it as =INDIRECT("Table1") in the data validation and =INDEX(Table1, 19) in B2.
- Don't mix different types of data sets on one sheet. You end up merging cells which is always a source of trouble. If you absolutely must, then use tables.
Note that you can name ranges within tables and use those names in addition to the named range comprising the entire table. You can also reference individual named columns of tables.
Note that named ranges can overlap. You will find that useful as you continue your development and start using VLOOKUP.
- Consider a radically different concept where your Comprehensive Expense Tracker is on a separate worksheet where you enter all your data (Same for the Income Tracker). You don't even need a new table for each month.
Then you create forms, which present the data extracted from the database. One such form would be your range A1:AD8 (or AD12). Each cell extracts data from the DB, not so different from what you have already designed but with the data on another sheet. By changing the date in A1 you see data from different months on the same form. By changing the tab you see another form, perhaps like your Debt Summary.