Vba Code

Z

ziad alsayed

Well-known Member
Joined
Jul 17, 2010
Messages
665
dear all

i have 3 Sheets the first is named "PHC 2011", the Second is named "Apapa 2011", the third is named "nigeria", the third Sheet get the Information from the first 2 sheets and Some them.

what i need is a VBA code that Work base on the cell "A1" in the "Nigeria" Sheet. the "A1" cell is a drop down list for month. so base on the "A1" the Code has to sum the data from the 2 Sheets "PHC 2011" and "Apapa 2011"

Appreciate Any Help
below is a Sample for the 3 Sheets
Excel Workbook
ABCDEFGHIJK
1
2PHC 2011
3SIZEJan/11Feb/11Mar/11
4RangeModelMTNMCTotalMTNMCTotalMTNMCTotal
5L12.5LPW3394584404080414182
6L17.5LPW4394584404080414182
7L17.5DSLPW4394584404080414182
8L17.5LRLPW4394584404080414182
9P13.5E2403C-15G394584404080414182
10P18E2404C-22G1394584404080414182
11P22E2404C-22G2394584404080414182
12P273.1524394584404080414182
13P27P11103A-33G1394584404080414182
14P40P11004G394584404080414182
15P40P31103A-33TG1394584404080414182
16P60P11004TG394584404080414182
17P60P31103A-33TG2394584404080414182
18P751004TG394584404080414182
19P801104-44TG2394584404080414182
20P100P11104-44TAG394584404080414182
21P110E1006TG2A394584404080414182
22P150E1006TAG394584404080414182
23P150P11006TAG1394584404080414182
24P160H1306-E87TA215394584404080414182
25P1801106394584404080414182
26P200H1306-E87TA300394584404080414182
27Total858990184888088017609029021804
PHC 2011
Excel 2010
Excel Workbook
ABCDEFGHIJK
1
2Apapa 2011
3SIZEJan/11Feb/11Mar/11
4RangeModelMTNMCTotalMTNMCTotalMTNMCTotal
5L12.5LPW321012336448
6L17.5LPW421012336448
7L17.5DSLPW421012336448
8L17.5LRLPW421012336448
9P13.5E2403C-15G21012336448
10P18E2404C-22G121012336448
11P22E2404C-22G221012336448
12P273.152421012336448
13P27P11103A-33G121012336448
14P40P11004G21012336448
15P40P31103A-33TG121012336448
16P60P11004TG21012336448
17P60P31103A-33TG221012336448
18P751004TG21012336448
19P801104-44TG221012336448
20P100P11104-44TAG21012336448
21P110E1006TG2A21012336448
22P150E1006TAG21012336448
23P150P11006TAG121012336448
24P160H1306-E87TA21521012336448
25P180110621012336448
26P200H1306-E87TA30021012336448
27Total4422026466661328888176
Apapa 2011
Excel 2010
Excel Workbook
ABCDE
1Jan/11
2NIGERIAGENSETS
3SIZENb. Of Gensets
4RangeModelMTNMCTotal
5L12.5LPW3415596
6L17.5LPW4415596
7L17.5DSLPW4415596
8L17.5LRLPW4415596
9P13.5E2403C-15G415596
10P18E2404C-22G1415596
11P22E2404C-22G2415596
12P273.1524415596
13P27P11103A-33G1415596
14P40P11004G415596
15P40P31103A-33TG1415596
16P60P11004TG415596
17P60P31103A-33TG2415596
18P751004TG415596
19P801104-44TG2415596
20P100P11104-44TAG415596
21P110E1006TG2A415596
22P150E1006TAG415596
23P150P11006TAG1415596
24P160H1306-E87TA215415596
25P1801106C-E66TAG4415596
26P200H1306-E87TA300415596
27Total90212102112
Nigeria
Excel 2010
Cell Formulas
RangeFormula
A5='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A5
A6='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A6
A9='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A9
A10='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A10
A11='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A11
A12='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A12
A13='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A13
A14='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A14
A15='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A15
A16='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A16
A17='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A17
A18='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A18
A19='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A19
A20='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A20
A21='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A21
A22='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A22
A23='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A23
A24='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A24
A26='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A26
B5='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B5
B6='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B6
B9='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B9
B10='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B10
B11='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B11
B12='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B12
B13='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B13
B14='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B14
B15='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B15
B16='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B16
B17='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B17
B18='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B18
B19='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B19
B20='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B20
B21='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B21
B22='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B22
B23='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B23
B24='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B24
B26='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B26
C5='PHC 2011'!C5+'Apapa 2011'!C5
C6='PHC 2011'!C6+'Apapa 2011'!C6
C7='PHC 2011'!C7+'Apapa 2011'!C7
C8='PHC 2011'!C8+'Apapa 2011'!C8
C9='PHC 2011'!C9+'Apapa 2011'!C9
C10='PHC 2011'!C10+'Apapa 2011'!C10
C11='PHC 2011'!C11+'Apapa 2011'!C11
C12='PHC 2011'!C12+'Apapa 2011'!C12
C13='PHC 2011'!C13+'Apapa 2011'!C13
C14='PHC 2011'!C14+'Apapa 2011'!C14
C15='PHC 2011'!C15+'Apapa 2011'!C15
C16='PHC 2011'!C16+'Apapa 2011'!C16
C17='PHC 2011'!C17+'Apapa 2011'!C17
C18='PHC 2011'!C18+'Apapa 2011'!C18
C19='PHC 2011'!C19+'Apapa 2011'!C19
C20='PHC 2011'!C20+'Apapa 2011'!C20
C21='PHC 2011'!C21+'Apapa 2011'!C21
C22='PHC 2011'!C22+'Apapa 2011'!C22
C23='PHC 2011'!C23+'Apapa 2011'!C23
C24='PHC 2011'!C24+'Apapa 2011'!C24
C25='PHC 2011'!C25+'Apapa 2011'!C25
C26='PHC 2011'!C26+'Apapa 2011'!C26
C27=SUM(C5:C26)
D5='PHC 2011'!D5+'Apapa 2011'!D5
D6='PHC 2011'!D6+'Apapa 2011'!D6
D7='PHC 2011'!D7+'Apapa 2011'!D7
D8='PHC 2011'!D8+'Apapa 2011'!D8
D9='PHC 2011'!D9+'Apapa 2011'!D9
D10='PHC 2011'!D10+'Apapa 2011'!D10
D11='PHC 2011'!D11+'Apapa 2011'!D11
D12='PHC 2011'!D12+'Apapa 2011'!D12
D13='PHC 2011'!D13+'Apapa 2011'!D13
D14='PHC 2011'!D14+'Apapa 2011'!D14
D15='PHC 2011'!D15+'Apapa 2011'!D15
D16='PHC 2011'!D16+'Apapa 2011'!D16
D17='PHC 2011'!D17+'Apapa 2011'!D17
D18='PHC 2011'!D18+'Apapa 2011'!D18
D19='PHC 2011'!D19+'Apapa 2011'!D19
D20='PHC 2011'!D20+'Apapa 2011'!D20
D21='PHC 2011'!D21+'Apapa 2011'!D21
D22='PHC 2011'!D22+'Apapa 2011'!D22
D23='PHC 2011'!D23+'Apapa 2011'!D23
D24='PHC 2011'!D24+'Apapa 2011'!D24
D25='PHC 2011'!D25+'Apapa 2011'!D25
D26='PHC 2011'!D26+'Apapa 2011'!D26
D27=SUM(D5:D26)
E5=SUM(C5:D5)
E6=SUM(C6:D6)
E7=SUM(C7:D7)
E8=SUM(C8:D8)
E9=SUM(C9:D9)
E10=SUM(C10:D10)
E11=SUM(C11:D11)
E12=SUM(C12:D12)
E13=SUM(C13:D13)
E14=SUM(C14:D14)
E15=SUM(C15:D15)
E16=SUM(C16:D16)
E17=SUM(C17:D17)
E18=SUM(C18:D18)
E19=SUM(C19:D19)
E20=SUM(C20:D20)
E21=SUM(C21:D21)
E22=SUM(C22:D22)
E23=SUM(C23:D23)
E24=SUM(C24:D24)
E25=SUM(C25:D25)
E26=SUM(C26:D26)
E27=SUM(C27:D27)
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
I think I would combine your headers into "Jan/11 MTN" "Jan/11 MC" etc.

Then do a HLOOKUP from A1 to your sheets. Just bring in MTC and MC and sum it on your nigeria sheet.
 
Upvote 0
dear all

i succeded to built the below Array formula

{=VLOOKUP($A5,'PHC 2011'!A:CH,MATCH(CONCATENATE(A1,C4),CONCATENATE('PHC 2011'!4:4,'PHC 2011'!5:5),0),0)}

it works for one branch, is possible to amend to give the sum of the two sheets. i mean instead of adding it as per below
{=VLOOKUP($A5,'PHC 2011'!A:CH,MATCH(CONCATENATE(A1,C4),CONCATENATE('PHC 2011'!4:4,'PHC 2011'!5:5),0),0)+VLOOKUP($A5,'Apapa 2011'!A:CH,MATCH(CONCATENATE(A1,C4),CONCATENATE('Apapa 2011'!4:4,'PHC 2011'!5:5),0),0)}


Appreciate any help
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
iferror function conditional formatting
Replies
1
Views
346
Joe4
Joe4
J
  • Solved
removing 0s from output and cell staying in date format
Replies
5
Views
348
Joe4
Joe4
S
  • Question Question
Need Help in a Data Table
2
Replies
14
Views
609
Santosh080
S
D
  • Question Question
VBA to Export Tab to .CSV with date
Replies
1
Views
222
abdelfattah
A
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
505
etaf
etaf

Forum statistics

Threads
1,224,617
Messages
6,179,914
Members
452,949
Latest member
beartooth91

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