So forgive me if this doesn't make sense, I am not sure exactly how to explain what I want to do. Basically this file is used to track transfers between departments, what we do is input each transfer into worksheet #2 (one line per item transferred) as we go through the month. For each line on worksheet #2 we input the Dept ID which populates the department description (using the lookup function but has no purpose for our purposes here), we input the inventory number, the number of transaction is always going to be 1 and the cost.
So the next part of this is to get the subtotals from each department in worksheet 2 inputted onto worksheet 1. There are too many departments to give them each their own section (which would greatly simplify this process) so what I want to do if possible is to make the cells in worksheet 1 recognize the dept id's in worksheet 2 and populate the subtotal in the correct departments on worksheet 1.
So in my examples below there are 2 transfers from Alpha listed on worksheet 2, right now I would have to sort and filter by department to gather the data I need for sheet 1. What I want is a formula that can be placed on sheet one that can read the dept id's and place the totaled amount into the correct field.
So Worksheet 1 will look like this:
| Code # | Department Name | J Acquisition | JD Disposal | Number of Transactions |
| A1 | ALPHA | | 45.00 | 2 |
| B2 | BETA | 20.00 | | 1 |
| C3 | GAMMA | 25.00 | | 1 |
Worksheet 2 looks like this
| Transfer From | | | | | Transfer To | | | | | |
| Dept | | | Number of
Transactions | | | Dept | | | Number of
Transactions | | | |
| Code | Department | Asset No | Cost | | Code | Department | Asset No | Cost | | |
| A1 | ALPHA | XXXX | 1 | 20.00 | | B2 | BETA | XXXX | 1 | 20.00 | | |
| A1 | ALPHA | OOOO | 1 | 25.00 | | C3 | GAMMA | OOOO | 1 | 25.00 | | |
| | | | | | | | | | | | | |
| | | | 2 | 45.00 | | | | | 2 | 45.00 | | |
I hope this makes sense, if not let me know and I will try to explain myself better.
