Need assistance please with complicated formula....

[ValpoMan]

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.....

	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.....

 

[Gilliam]

If I were to lookup the Company #1, where would I find it in column 3? Which header?
I need company #'s and their profit centers adjacent to it to match (on tab 3)

 

[oldbrewer]

is company 1 cost centre number defined ?

 

[ValpoMan]

Company 1 is profit center 10120
Company 2 is profit center 10125
Company 3 is profit center 10230
Company 4 is a little trickier...they have 3 profit centers 10122, 10123, 10124

You will not see these profit centers in my small sample that I posted above of TAB 3 but that sheet is about a thousand lines long....

Here is the data from TAB 3 for company #1:

Cost Center		Company Code	Functional Area	Object Currency	Profit Center
1001310	Facilities KOP / LOP	1150	SGADMIN	USD	10120
1001322	Client Support	1150	SERVICE	USD	10120
1001328	Business Analyst	1150	SERVICE	USD	10120
1001331	Core Technology	1150	SERVICE	USD	10120
1001332	Integration	1150	SERVICE	USD	10120
1001335	Project Mgt Office	1150	SERVICE	USD	10120
1001337	Quality Assurance	1150	SERVICE	USD	10120
1001349	Architecture	1150	SERVICE	USD	10120
1001351	Software Engineering	1150	SERVICE	USD	10120
1001353	SCM/SW Eng	1150	SERVICE	USD	10120
1001354	Tech Support/SW Eng	1150	SERVICE	USD	10120
1001356	ASP Operations	1150	SERVICE	USD	10120
1001357	Information/Tech (IT	1150	SERVICE	USD	10120
1001358	Accruals/Allocations	1150	SERVICE	USD	10120

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

 

[ValpoMan]

Each of the companies/profit centers have about 10-15 cost centers that are unique to their company....they are mixed between SGADMIN and SERVICE (COR)....