Reading in data from categoris on separate sheet

0

Hello All,

I am new to the Forum and to Macros.  I'm making a budget spreadsheet that has one sheet with a list of monthly expense categories (budget and actual), and another sheet with a list of actual monthly transactions.  For the latter I indlucde the amount in one column and a category type in the next column that is equivalent to one in the expense category sheet. 

What I would like to do is create something that will filter the list on the transactions sheet and sum the totals from any given category into their counterpart on the expense categories sheet.

I'm assuming there is a way to write a macro to do this.  Can anyone point me to a tutorial or discussion that can get me started?

-Justin

Answer
Discuss

Answers

0

The way to go about this is to first create the system and then, in the second step, automate it. Most novices will try to do both at the same time. That will lead to a flawed design which causes permanent trouble and the only cure being to start afresh.

Your design appears correct but might require a slightly different view. Consider describing it like this:-

  1. There is one sheet containing the budget.
    I would recommend it to be an annual budget, perhaps with a column for each month for each category. This sheet doesn't need the actual.
  2. There is another sheet with actual expenditure where each item is identified with date, category and amount. This sheet would an endless database, entries not separated by month but identified by date. If you absolutely must, start a new sheet at the beginning of a year but chances are that you will soon find that troublesome.
  3. There is a third sheet for reconciliation. This sheet takes the budget from the first sheet and summarizes the actuals from the second. You will want to be able to define a month for reconciliation or a group of months, up to a year.
  4. There may be a fourth "sheet" which shows the budget per month, or group of months. This would be an extract from the first sheet to give a different presentation, probably in portait format where the first sheet is landcape becauswe of its many columns. I refer to it as "sheet" because it really isn't. It's the template for a report. You would use code to extract data from the budget sheet and write them into the template sheet which contains the cell formatting (to reduce the amount of code needed).

Code can only be written when the design (number of columns, column captions, cell formatting) is complete. It consists then of finding a data row and copying the value from one column to a particular cell on another sheet. VBA makes this very easy. You will need code like

Worksheets("Reconciliation").Cells([Row], [Column]).Value = Worksheets("Budget").Cells([Row], [Column]).Value

or

MySum = MySum + Worksheets("Budget").Cells([Row], [Column]).Value
and
Worksheets("Reconciliation").Cells([Row], [Column]).Value = MySum

To get started, make sure that everything you need to refer to has a name. Then describe what you want summed up and written where in plain English, and someone will show you the syntax. In next to no time you will be able to do it on your own.

Discuss

Discussion

Thanks, I'll chew on this and see if I can't make it work!  Thanks for feedback!
J Cole Jun 2, '19 at 8:20 pm
Add to Discussion

Answer the Question

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