Hi I am using excel 2007 and i am having problem on what formula to use on my third sheet...
my first sheet is my Main Sheet where I input data then, a button for encoding.
then after encoding the data will be encoded on my masterlist, second sheet
then this is my problem what formula to use on Cell A to M particularly starting from rows 3 downward. The scenario is this if i pick a year on cell D1(list down box/cell of years), the count of delivery in respective delivery boy and month will appear. the delivery boy is a name of a true person.
this is the sample sheet.
in year 2011
this is the sample sheet.
in year 2011
Please please help me!!!!!!!!!!!
Thank you in advance...
my first sheet is my Main Sheet where I input data then, a button for encoding.
Excel Workbook | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | * | * | * | * | * | * | ||
2 | * | poducts | * | * | ||||
3 | * | * | * | * | * | * | ||
4 | * | date delivered | * | * | * | * | ||
5 | * | * | month | Date | Year | * | ||
6 | * | location of delivery | * | * | ||||
7 | * | * | * | * | * | * | ||
8 | * | delivered by | * | * | ||||
9 | * | * | * | * | * | * | ||
10 | * | sales | * | * | ||||
11 | * | * | * | * | * | * | ||
12 | * | * | Encode | * | * | |||
13 | * | * | * | * | * | * | ||
MAIN Sheet |
then after encoding the data will be encoded on my masterlist, second sheet
Excel Workbook | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Products | Date Delivered | Location of Delivery | Delivered by | Sales | ||||
2 | Month | Day | Year | ||||||
3 | Shoes1 | June | 8 | 2010 | Area 1 | Boy 7 | 1k | ||
4 | Shoes2 | December | 17 | 2010 | Area 2 | Boy 2 | 1k | ||
5 | Shoes3 | August | 7 | 2010 | Area 3 | Boy 3 | 1k | ||
6 | Undrewears1 | September | 5 | 2010 | Area 5 | Boy 22 | 1k | ||
7 | Undrewears2 | October | 5 | 2010 | Area 5 | Boy 5 | 1k | ||
8 | Undrewears3 | November | 16 | 2010 | Area 2 | Boy 6 | 1k | ||
9 | Undrewears4 | December | 5 | 2010 | Area 3 | Boy 7 | 1k | ||
10 | Undrewears5 | June | 5 | 2012 | Area 2 | Boy 8 | 1k | ||
11 | Sandals1 | September | 25 | 2010 | Area 1 | Boy 12 | 1k | ||
12 | Sandals2 | October | 25 | 2010 | Area 2 | Boy 10 | 1k | ||
13 | Sandals3 | November | 25 | 2010 | Area 3 | Boy 11 | 1k | ||
14 | Towels1 | December | 22 | 2010 | Area 4 | Boy 7 | 1k | ||
15 | Towels2 | January | 25 | 2010 | Area 5 | Boy 3 | 1k | ||
16 | Towels3 | June | 5 | 2010 | Area 2 | Boy 1 | 1k | ||
17 | Towels4 | May | 7 | 2011 | Area 1 | Boy 13 | 1k | ||
18 | Towels5 | June | 5 | 2010 | Area 2 | Boy 6 | 1k | ||
19 | Towels6 | August | 21 | 2010 | Area 2 | Boy 10 | 1k | ||
20 | Towels7 | June | 9 | 2011 | Area 4 | Boy 2 | 1k | ||
21 | Towels7 | July | 14 | 2011 | Area 5 | Boy 1 | 1k | ||
22 | Shorts1 | August | 9 | 2011 | Area 1 | Boy 9 | 1k | ||
23 | Shorts2 | June | 14 | 2011 | Area 5 | Boy 9 | 1k | ||
24 | Shorts2 | June | 14 | 2011 | Area 1 | Boy 4 | 1k | ||
25 | * | * | * | * | * | * | * | ||
26 | * | * | * | * | * | * | * | ||
27 | * | * | * | * | * | * | * | ||
Masterlist |
then this is my problem what formula to use on Cell A to M particularly starting from rows 3 downward. The scenario is this if i pick a year on cell D1(list down box/cell of years), the count of delivery in respective delivery boy and month will appear. the delivery boy is a name of a true person.
this is the sample sheet.
in year 2011
Excel Workbook | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Delivered by | Select a year | 2011 | Summary of Delivery per Delivery Boy | |||||||||||
2 | January | February | March | April | May | June | July | August | September | October | November | December | |||
3 | Boy 13 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
4 | Boy 2 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ||
5 | Boy 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | ||
6 | Boy 9 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | ||
7 | Boy 4 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ||
8 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
9 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
10 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
11 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
12 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
13 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
14 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
15 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
16 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
17 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
18 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
19 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
20 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
21 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
22 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
23 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
24 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
25 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Delivery Count per Delivery Boy |
this is the sample sheet.
in year 2011
Excel Workbook | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Delivered by | Select a year | 2012 | Summary of Delivery per Delivery Boy | |||||||||||
2 | January | February | March | April | May | June | July | August | September | October | November | December | |||
3 | Boy 8 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ||
4 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
5 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
6 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
7 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
8 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
9 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
10 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
11 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
12 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
13 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
14 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
15 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
16 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
17 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
18 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
19 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
20 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
21 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
22 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
23 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
24 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
25 | * | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Delivery per Delivery Boy |
Please please help me!!!!!!!!!!!
Thank you in advance...