Morning,
Thanks in advance! Ok here is the lay of the land.....
I have 3 tabs in a spreadsheet:
Tab 1 is just a simple summary sheet....this is where I am trying to put the formula.....
<tbody>
</tbody>
Company 1 has a specific set of cost centers assigned to it but only ONE profit center....Company 2 has a different set and so on....
Tab 2 is a sheet with data exported from my accounting software....2 columns that I want the formula to reference are COST CENTER and AMOUNT
Here is a small sample of the layout:
<tbody>
</tbody>
Tab 3 is a sheet that I want to serve as a lookup tab....columns important here are COST CENTER, FUNCTIONAL AREA (SGA/COR), and PROFIT CENTER.
Here is a small sample of the layout:
<tbody>
</tbody>
So what I am trying to come up with in a formula is the following.....I will use TAB 1 Company #1 and the SGA cell for the sake of discussion
* Search TAB 3 for the profit center for company #1....look to see if it has a FUNCTIONAL AREA labeled as SGADMIN....if so then somehow retain the COST CENTER list in memory.....
* then take that list of cost centers and add the amounts for those cost centers in TAB 2
Sorry if this is messy....I wanted to try to provide a good picture of what I have.....
Thanks in advance! Ok here is the lay of the land.....
I have 3 tabs in a spreadsheet:
Tab 1 is just a simple summary sheet....this is where I am trying to put the formula.....
CO#1 | CO#2 | CO#3 | CO#4 | TOTAL | |
SGA | formula | formula | formula | formula | $ - |
COR | formula | formula | formula | formula | $ - |
TOTAL | $ - | $ - | $ - | $ - | $ - |
<tbody>
</tbody>
Company 1 has a specific set of cost centers assigned to it but only ONE profit center....Company 2 has a different set and so on....
Tab 2 is a sheet with data exported from my accounting software....2 columns that I want the formula to reference are COST CENTER and AMOUNT
Here is a small sample of the layout:
Cost Center | Posting Date | Document Header Text | Name | Amount |
1001312 | 6/30/2014 | SD AC ATT MOBILITY CHARGE | AC ATT MOBILITY JUN 2014 | 39.09 |
1001312 | 6/1/2014 | SD AC ATT MOBILITY CHARGE | AC ATT MOBILITY MAY 2014 | -39.09 |
1001312 | 6/30/2014 | SD AC VZ VERIZON WIRELESS | AC VERIZON WIRELESS JUN 2014 | 175.47 |
1001312 | 6/1/2014 | SD AC VZ VERIZON WIRELESS | AC VZ VERIZON WIRELESS MAY 2014 | -154.74 |
1001312 | 6/30/2014 | SD ATT MOBILITY CHARGE MA | ATT MOBILITY MAY 2014 | 39.09 |
1001312 | 6/30/2014 | SD VZ VERIZON WIRELESS CH | VERIZON WIRELESS MAY 2014 | 175.47 |
1001314 | 6/30/2014 | SD AC VZ VERIZON WIRELESS | AC VERIZON WIRELESS JUN 2014 | 230.41 |
<tbody>
</tbody>
Tab 3 is a sheet that I want to serve as a lookup tab....columns important here are COST CENTER, FUNCTIONAL AREA (SGA/COR), and PROFIT CENTER.
Here is a small sample of the layout:
Cost Center | Company Code | Functional Area | Object Currency | Profit Center | |
1000001 | Rev & Cost of Rev | 1250 | SERVICE | USD | 10011 |
1000002 | Sales Support | 1250 | SGADMIN | USD | 10011 |
1000003 | Documentation | 1250 | SERVICE | USD | 10011 |
1000004 | Training | 1250 | SERVICE | USD | 10011 |
1000005 | Client Services | 1250 | SERVICE | USD | 10011 |
1000006 | Account Management | 1250 | SGADMIN | USD | 10011 |
1000007 | Conversions | 1250 | SERVICE | USD | 10011 |
1000008 | Conversions | 1250 | SERVICE | USD | 10011 |
1000009 | Risk Management | 1250 | SGADMIN | USD | 10011 |
1000010 | Premier Ecom (Esol) | 1250 | SERVICE | USD | 10011 |
1000011 | Branch Capture | 1250 | SERVICE | USD | 10011 |
1000012 | Product Development | 1250 | SERVICE | USD | 10011 |
<tbody>
</tbody>
So what I am trying to come up with in a formula is the following.....I will use TAB 1 Company #1 and the SGA cell for the sake of discussion
* Search TAB 3 for the profit center for company #1....look to see if it has a FUNCTIONAL AREA labeled as SGADMIN....if so then somehow retain the COST CENTER list in memory.....
* then take that list of cost centers and add the amounts for those cost centers in TAB 2
Sorry if this is messy....I wanted to try to provide a good picture of what I have.....