Hi - any help on this would be much appreciated.
Ultimate goal: I have a company producing detailed results by cost center, by site. I wish to create a consolidated result, which is sorted by cost type, by division. I wish to create this so it is user-friendly in the future, so a user can simply add a tab, paste the detailed spread in, and then the worksheet automatically consolidates the results based on pre-determined mapping.
Eg.
Tab 1 = Detailed Result ("PL_May_2013")
A B C D E F
1 Costs Site1 Site2 Site3 Site4 Site5
2 Cost1 5 6 2 3 2
3 Cost2 7 8 2 3 5
4 Cost3 7 8 2 3 5
5 Cost4 7 8 2 3 5
Tab 2 = Costs by Type Mapping ("GL")
A B
1 Costs Type
2 Cost1 Type1
3 Cost2 Type1
4 Cost3 Type2
5 Cost4 Type3
Tab 3 = Sites by Division Mapping ("Divisions")
A B
1 Site Division
2 Site1 D1
3 Site2 D1
4 Site3 D2
5 Site4 D3
Finally, Tab 4 = Results ("Active Worksheet")
A B C D
1 Type D1 D2 D3
2 Type1 21 4 6
3 Type2 15 2 3
4 Type3 15 2 3
I can make this work quite well with the following array formula;
=SUMPRODUCT(--ISNUMBER(MATCH(PL_May_2013!B9:B425,IF(GL!$C$2:$C$880=C9,GL!$D$2:$D$880,0),0))*(--ISNUMBER(MATCH(PL_May_2013!$G$2:$BD$2,IF(Divisions!A2:A50="Plastics Group ",Divisions!C2:C50,0),0)))*PL_May_2013!G9:BD425)
The issue is that the detailed result shows every cost as a positive number, whereas some are actually negatives. To counter this, in the "GL" mapping tab, I have added an additional column which denotes the cost as a "+1" or "-1", intention being to add this as a final criteria to the SUMPRODUCT. I can't get this formula to work. I have tried adding the following to my formula:
=SUMPRODUCT(--ISNUMBER(MATCH(PL_May_2013!B10:B426,IF(GL!$C$2:$C$880=C9,GL!$D$2:$D$880,0),0))*IFERROR(OFFSET(GL!D1,MATCH(PL_May_2013!B10:B426,IF(GL!$C$2:$C$880=C9,GL!$D$2:$D$880,0),0),2),0)*(--ISNUMBER(MATCH(PL_May_2013!$G$2:$BD$2,IF(Divisions!A3:A51="Plastics Group ",Divisions!C3:C51,0),0)))*PL_May_2013!G10:BD426)
However, the "IFERROR" seems to convert the entire array to zeros, eg. {0;0;0;0;0;0;0} and so forth, even though the OFFSET shows [-1, 1, -1, 1, 1, N/A, N/A, etc.}
Understand this is highly complex - any thoughts would be very much appreciated. Understand it would be easier for me to post the spreadsheet, but I'd prefer not to given its sensitive info.
Bookmarks