I need help on what formula to use!!!

doms123 · May 5, 2011

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 and 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 the second sheet, the Masterlist 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

Masterlist Sheet

and my problem is on my third sheet or the Summary of Delivery per Delivery Boy i am having a trouble on 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

1

0

4

Boy 2

0

1

0

5

Boy 1

0

1

0

6

Boy 9

0

1

0

1

0

7

Boy 4

0

1

0

8

0

9

0

10

0

11

0

12

0

13

0

14

0

15

0

16

0

17

0

18

0

19

0

20

0

21

0

22

0

23

0

24

0

25

0

Delivery per Delivery Boy

here is another example of the third sheet
in year 2012

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

1

0

4

0

5

0

6

0

7

0

8

0

9

0

10

0

11

0

12

0

13

0

14

0

15

0

16

0

17

0

18

0

19

0

20

0

21

0

22

0

23

0

24

0

25

0

Delivery per Delivery Boy

Please please please help me!!!!!!!!!!!
Thank you in advance...

pkotzee · May 5, 2011

I'm not sure I understand your problem, but you might try to setup a pivottable from your Masterlist data. you would then use the getpivotdata formula in each cell in your 'summary of delivery boy' sheet. you can set it up that when you select the different years (I presume that you use a dropdown list) the cells with the getpivotdata formulas will 'get' the figures from your pivottable. it sounds complicated but works great if it is setup correctly.

doms123 · May 5, 2011

i think my data is I very detailed(I i select a year, that is located on D1, the name will be displayed on the column A, and the count of delivery on its respective month will also be displayed, that's what i need...), i am not sure on a pivot table because i do not know how to use it, and its way complicated, i am just asking for a formula on what to use on A3:M10000. I am using this is making my project database

The data is linked on the second sheet, my Masterlist. so that if i change the data on the masterlist it will be automatically be updated on the 3rd sheet.

Please please please Help me!!!
Thank you in advance....

crook_101 · May 5, 2011

Hi,

I can help you get to the following result using a pivot table - this will look like this:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Year</TD><TD>2010</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Count of Delivered by</TD><TD>Column Labels</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>Row Labels</TD><TD>January</TD><TD>June</TD><TD>August</TD><TD>September</TD><TD>October</TD><TD>November</TD><TD>December</TD><TD>Grand Total</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Boy 1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>Boy 10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>Boy 11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD>Boy 12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD>Boy 2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD>Boy 22</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD>Boy 3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD>Boy 5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD>Boy 6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD>Boy 7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD>Grand Total</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">15</TD></TR></TBODY></TABLE>

Sheet7

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Year</TD><TD>2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Count of Delivered by</TD><TD>Column Labels</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>Row Labels</TD><TD>May</TD><TD>June</TD><TD>July</TD><TD>August</TD><TD>Grand Total</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Boy 1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>Boy 13</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>Boy 2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD>Boy 4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD>Boy 9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD>Grand Total</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">6</TD></TR></TBODY></TABLE>

Sheet7

Let me know but it will mean unmerging some of youe merged headers on your 2nd sheet........

A formulaic approach is, IMHO, a non starter due the complexity of the formula and the number of vaeiables involved. However if that is your wish, please continue looking for a solution.

Cheers,
Ian

doms123 · May 5, 2011

but how you done it on the pivot table??? cause I dont know how to use a pivot table...

I need help on what formula to use!!!

doms123

Board Regular

Excel Facts

pkotzee

New Member

doms123

Board Regular

crook_101

Well-known Member

doms123

Board Regular

Similar threads

Forum statistics

Share this page

I need help on what formula to use!!!

doms123

Board Regular

Excel Facts

pkotzee

New Member

doms123

Board Regular

crook_101

Well-known Member

doms123

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock