Excel 2002 VLOOKUP formula or other formula

[Guest]

I have an "Input sheet" with various bolt diameters, bolt grades & bolt
lengths.
example:
D E G H
J
Dia. Grade (Flat Washer) (Beveled washer) Length
..625 A325 black 1
1.5
..5 A325 galv 1
1.75
..75 SAE 1
2.0
..75 A325 galv 1
2.25
..75 A325 black 1
2.0

In the output sheet (same workbook) one area is assigned for each bolt
grade that has a flat washer, one for beveled washer:
Sample:
A325 HEX HEAD BOLT ASSY (Galv with Nut & Flat Washer)
A B C
Dia Length Qty
row 19 (formula ?) (formula ?)
row 20 (formula ?) (formula ?)
row 21 (formula ?) (formula ?)
etc. etc.
I column "C" there is a formula that will give a quantity for that specific
bolt grade providing I manually input the "bolt dia" in "A" and the "bolt
length" in "B" to match the information on the input sheet.
I would like to retreive the Dia & length in the first row and some how in
the second row the formula needs to look for different bolt dia that may have
different or same length as the previous row and so on. Column "C" will
simply give me a quantity for those bolts.

From previous posting I tried pivot table suggestions but my brain just
caanot get arround this.

Could someone help please, I'm realy desperate.

Thanks

Serge

 

[Debra Dalgleish]

Create a pivot table from the data, with Diameter in the row area,
length in the column area, and Count of Diameter in the data area.
In the page area, add Flat washer, and from its dropdown list, choose 1

Copy that pivot table, and paste the copy on a different sheet.
In the copy, remove Flat washer from the page area, and add Beveled
washer, and from its dropdown list, choose 1.

 

[Guest]

Good Morning Debra,
Thanks for your reply.
You lost me, when I read your answer it sounds so simple but I don't see how
it relates to A19 & A20 in my sample.
I'm looking to find a way for those two cells to populate automatically from
the input sheet

 

[Debra Dalgleish]

In your sample data, you don't show a column for the description, but
perhaps that's the Grade column, or there are other columns in the
source data.
You could add that description field to the pivot table's page area as
well, and select a description from its dropdown list.
Then, select a description, and the pivot table would show all the
diameters and lengths with a total quantity for the selected description.

 

[Guest]

Good Morning Debra,
The sample shown is only one of six in the output sheet called the Summary
sheet. The input sheet called the Bolt List sheet where the user inputs: Dia,
Grade and wheather it requires a Flat or Beveled Washer or both, then column
J will indicate the length base on the grip provided.
Now on the Summary sheet six areas have been separated as follows:A325 Black
Assy with Nut & FW, A325 Black with Nut & BW, A325 Galv with Nut & FW, A325
Galv with BW, SAE Bolts etc, etc.
In column A & B in my sample I was looking for a formula that would
eliminate user input for the diameter & length & populate those two columns
from the input sheet automatically. I guess mabe I'm asking the impossible.
I will check on any replies when I get home after work.
Thanks
Serge