Need assistance please with complicated formula....

V

ValpoMan

New Member
Joined
Jun 25, 2010
Messages
46
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.....

[TABLE="width: 288"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]CO#1
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]CO#2
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]CO#3
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]CO#4
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]TOTAL
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SGA
[/TD]
[TD="class: xl65, bgcolor: yellow"] formula
[/TD]
[TD="class: xl65, bgcolor: yellow"] formula
[/TD]
[TD="class: xl65, bgcolor: yellow"] formula
[/TD]
[TD="class: xl65, bgcolor: yellow"] formula
[/TD]
[TD="class: xl64, bgcolor: transparent"] $ -
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COR
[/TD]
[TD="class: xl65, bgcolor: yellow"] formula
[/TD]
[TD="class: xl65, bgcolor: yellow"] formula
[/TD]
[TD="class: xl65, bgcolor: yellow"] formula
[/TD]
[TD="class: xl65, bgcolor: yellow"] formula
[/TD]
[TD="class: xl64, bgcolor: transparent"] $ -
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TOTAL
[/TD]
[TD="class: xl64, bgcolor: transparent"] $ -
[/TD]
[TD="class: xl64, bgcolor: transparent"] $ -
[/TD]
[TD="class: xl64, bgcolor: transparent"] $ -
[/TD]
[TD="class: xl64, bgcolor: transparent"] $ -
[/TD]
[TD="class: xl64, bgcolor: transparent"] $ -
[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 717"]
<tbody>[TR]
[TD]Cost Center
[/TD]
[TD]Posting Date
[/TD]
[TD]Document Header Text
[/TD]
[TD]Name
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]1001312
[/TD]
[TD]6/30/2014
[/TD]
[TD]SD AC ATT MOBILITY CHARGE
[/TD]
[TD]AC ATT MOBILITY JUN 2014
[/TD]
[TD]39.09
[/TD]
[/TR]
[TR]
[TD]1001312
[/TD]
[TD]6/1/2014
[/TD]
[TD]SD AC ATT MOBILITY CHARGE
[/TD]
[TD]AC ATT MOBILITY MAY 2014
[/TD]
[TD]-39.09
[/TD]
[/TR]
[TR]
[TD]1001312
[/TD]
[TD]6/30/2014
[/TD]
[TD]SD AC VZ VERIZON WIRELESS
[/TD]
[TD]AC VERIZON WIRELESS JUN 2014
[/TD]
[TD]175.47
[/TD]
[/TR]
[TR]
[TD]1001312
[/TD]
[TD]6/1/2014
[/TD]
[TD]SD AC VZ VERIZON WIRELESS
[/TD]
[TD]AC VZ VERIZON WIRELESS MAY 2014
[/TD]
[TD]-154.74
[/TD]
[/TR]
[TR]
[TD]1001312
[/TD]
[TD]6/30/2014
[/TD]
[TD]SD ATT MOBILITY CHARGE MA
[/TD]
[TD]ATT MOBILITY MAY 2014
[/TD]
[TD]39.09
[/TD]
[/TR]
[TR]
[TD]1001312
[/TD]
[TD]6/30/2014
[/TD]
[TD]SD VZ VERIZON WIRELESS CH
[/TD]
[TD]VERIZON WIRELESS MAY 2014
[/TD]
[TD]175.47
[/TD]
[/TR]
[TR]
[TD]1001314
[/TD]
[TD]6/30/2014
[/TD]
[TD]SD AC VZ VERIZON WIRELESS
[/TD]
[TD]AC VERIZON WIRELESS JUN 2014
[/TD]
[TD]230.41
[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 581"]
<tbody>[TR]
[TD]Cost Center
[/TD]
[TD][/TD]
[TD]Company Code
[/TD]
[TD]Functional Area
[/TD]
[TD]Object Currency
[/TD]
[TD]Profit Center
[/TD]
[/TR]
[TR]
[TD]1000001
[/TD]
[TD]Rev & Cost of Rev
[/TD]
[TD]1250
[/TD]
[TD]SERVICE
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000002
[/TD]
[TD]Sales Support
[/TD]
[TD]1250
[/TD]
[TD]SGADMIN
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000003
[/TD]
[TD]Documentation
[/TD]
[TD]1250
[/TD]
[TD]SERVICE
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000004
[/TD]
[TD]Training
[/TD]
[TD]1250
[/TD]
[TD]SERVICE
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000005
[/TD]
[TD]Client Services
[/TD]
[TD]1250
[/TD]
[TD]SERVICE
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000006
[/TD]
[TD]Account Management
[/TD]
[TD]1250
[/TD]
[TD]SGADMIN
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000007
[/TD]
[TD]Conversions
[/TD]
[TD]1250
[/TD]
[TD]SERVICE
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000008
[/TD]
[TD]Conversions
[/TD]
[TD]1250
[/TD]
[TD]SERVICE
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000009
[/TD]
[TD]Risk Management
[/TD]
[TD]1250
[/TD]
[TD]SGADMIN
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000010
[/TD]
[TD]Premier Ecom (Esol)
[/TD]
[TD]1250
[/TD]
[TD]SERVICE
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000011
[/TD]
[TD]Branch Capture
[/TD]
[TD]1250
[/TD]
[TD]SERVICE
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
[TR]
[TD]1000012
[/TD]
[TD]Product Development
[/TD]
[TD]1250
[/TD]
[TD]SERVICE
[/TD]
[TD]USD
[/TD]
[TD]10011
[/TD]
[/TR]
</tbody>[/TABLE]



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

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
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)
 
Upvote 0
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:

[TABLE="width: 581"]
<tbody>[TR]
[TD]Cost Center[/TD]
[TD] [/TD]
[TD]Company Code[/TD]
[TD]Functional Area[/TD]
[TD]Object Currency[/TD]
[TD]Profit Center[/TD]
[/TR]
[TR]
[TD]1001310[/TD]
[TD]Facilities KOP / LOP[/TD]
[TD]1150[/TD]
[TD]SGADMIN[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001322[/TD]
[TD]Client Support[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001328[/TD]
[TD]Business Analyst[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001331[/TD]
[TD]Core Technology[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001332[/TD]
[TD]Integration[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001335[/TD]
[TD]Project Mgt Office[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001337[/TD]
[TD]Quality Assurance[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001349[/TD]
[TD]Architecture[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001351[/TD]
[TD]Software Engineering[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001353[/TD]
[TD]SCM/SW Eng[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001354[/TD]
[TD]Tech Support/SW Eng[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001356[/TD]
[TD]ASP Operations[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001357[/TD]
[TD]Information/Tech (IT[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
[TR]
[TD]1001358[/TD]
[TD]Accruals/Allocations[/TD]
[TD]1150[/TD]
[TD]SERVICE[/TD]
[TD]USD[/TD]
[TD]10120[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]
 
Upvote 0
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)....
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Sumifs
Replies
5
Views
378
Redwolfx
R
J
  • Question Question
Extract data using date parameters
Replies
2
Views
845
bhos123
B
M
  • Question Question
Averages and Max values if certain criteria are selected
Replies
1
Views
554
rilzniak
R
J
  • Question Question
Hi i need a formula please
Replies
0
Views
281
john_cloudstrong
J
B
  • Question Question
my mind is a blank, so i need some help please
Replies
5
Views
420
bionicle
B

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top