Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to get variables linked to a dollar amount and then added to a different colum



Im new to excel so Im not sure if my question is even decriptive of what I actually want. 

I created an excel spread sheet by watching some videos on line however I want to take it further but lack the knowledge. 

In the "Comprehensive Expence Tracker"  I have created a drop down menu allowing me to pick which expense category corresponds to the amount i have spent. 

What I want to do is get the amount to populate a total in the SPENT columl at the top of the page. 

So for example:

If i paid/ entered 585.00 (B30) and select the category "mortgage" (D30) I want 585.00 (B30) to be carried up to the "spent" under the corresponding category, in this case "Mortgage" (B7).

I would like to do this for all the categories so they auto popular in their respective cells. Also, being a budget please consideration that there maybe more then one expense recorded for a category. Please look at the month of JUNE 

I hope that made sense!!!

I am gratefull for any help!




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.

  1. 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.
  2. 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.
  3. 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.


Thank you for your advise! I will defiantly look into your suggestions! And the formula worked! Thank you so much! 
ashbigs (rep: 2) Jun 8, '18 at 12:00 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login