Hello all, I hope somone one can lead us in the right directions. Here is our situation:
We are creating an expense tracking excel file for our projects. We have one worksheet that has all the accounts for this project with columns for budgeted and actuals. We also have a different worksheet where we will enter the individual expenses. We need to write a formula or macro that will take each new expense, identify its category, and then add the amount the the 'total' of the correct account on the Budget vs. Actual Worksheet.
For example. This is how the budget vs. Actual would look like
--------------
Account | description | Budget | Actual
1100 | Director | 8000 | 0
1120 | Asist Dir | 5000 | 0
etc
----------------
Then we have the expense worksheet with the following columns
---------------
Date | Account | Description | Amount
---------------
After an expense we go to the expense worksheet and enter the expense:
---------------
Date | Account | Description | Amount
11/1 | 1100 | Partial Payment | 2000
---------------
We want the budget vs actual worksheet to reflect this payment automatically:
--------------
Account | description | Budget | Actual
1100 | Diretcor | 8000 | 2000
1120 | Asist Dir | 5000 | 0
etc
----------------
We want this to be commulative, so that the next expense:
---------------
Date | Account | Description | Amount
11/2 | 1100 | Partial Payment | 3000
---------------
Is then added to the Budget vs Actual worksheet:
--------------
Account | description | Budget | Actual
1100 | Director | 8000 | 5000
1120 | Asist Dir | 5000 | 0
etc
----------------
So in effect what we need is a formula at in the "actual x account 1100" cell that sums the "Amount" column of every entry on the "Expense" worksheet that has an account number 1100.
Any ideas how to go there?
Thank you so much NLD.
Bookmarks