Counting numbers in a table

[Arwes]

Hi,

Situation:
I'm using Excel 2002 and work in a public traffic company with 572 busses,
devided over 14 different locations.
A lot of windshields have to be replaced on those busses and before we can
start the job, we have to count how many windshields we have to order.
There are 25 different types of busses and not all the busses have the same
number of windshields.

In the worksheet [Busses] I collect the info about the windshields to be
replaced:
Column A: Buss_Number
Column B: Location
Column C: Buss_Type
Column D: No_WS (no windshield to be replaced)
Column E: WS1 (Windshield 1 to be replaced)
Column F: WS2
Column G: WS3
Column H: WS4
Column I: WS5

In the columns D we put a '0', meaning that we've seen the buss and there is
no windshield to replace.
In the columns E:I we put a code to point to the windshield that we have to
replace. For example: L1 (first window on the left), R4 (fourth window on
the right).....
In this table we assume that we don't have to replace more then 5
windshields on one buss.

In another worksheet [WS] I a have a table with all the part numbers of all
the windshields from all different types of busses.
A2:A26 different buss types (f.e. Type1, Type2,....)
B1:U1 the code to point to the windows (L1:L10 and R1:R10)
At the intersection there is the part number.

Problem:
I'm trying to gererate a report that gives me the number of each different
part number that I have to order.

So far I tryed with a pivot table, but there are 2 different tables
involved.
I used the VLookup and the intersection operator.

Can someone put me on the right track here?

Thnx

Arwes

 

[Guest]

You are really close but I think you need to tweek your raw data a tad. You
have two sheet. Replacement data, and part numbers...

Replacemetn data should be organized mroe like this:
Column A: Buss_Number
Column B: Location
Column C: Buss_Type
Column D: No_WS (no windshield to be replaced)
Column E: WS1 (Windshield 1 to be replaced)
Column F: Part Number

Your data will look something like this...
Bus # Location Bus Type Replace WS # Part Number
1234 Here Yellow TRUE L3 BW01234GTN
1234 Here Yellow TRUE L4 BW01234GTN
9876 There Green FALSE

Now based on the Bus type and WS# you should be able to look up the Part
number. This data will pivot very well. One more benefit is that you will not
be limited to 5. The more rows for any one bus the more windshields it
needs...

HTH

Bus