On "Bolt List" Sheet I have as shown below

Qty Dia Grade Flat Washer Length
C D E G J
_______________________________________________
6 0.750 A325 Galv 1 1.75
6 .625 A325 Galv 1 2.0
8 .50 A325 Galv 1 2.25

On "Summary Sheet" I have as shown next
Dia Length Qty
A B C
_______________________


In column C I have the following formula =SUMPRODUCT(('Bolt
List'!$D$9:$D$188=A19)*('Bolt List'!$J$9:$J$188=B19)*('Bolt
List'!$E$9:$E$188="A325 GALV.")*(ISNUMBER('Bolt List'!$G$9:$G$188))
*'Bolt List'!$C$9:$C$188)
that will give me the quantities for each diameter and length that are user
input in " A" & in "B"
Which require the user to constanly check as he populates the "Bolt List"
sheet to input the two columns in the "Summary Sheet"

This workbook works well as is but I want to eliminate user input on the
"Summary Sheet" by having rows from columns 'A' & 'B' populate automatically.

Am I asking the impossible? Can someone help PLEASE?

I assume that you want to list in "Summary" all the combinations of
diameters and lengths that appear in "Bolt List", selected by "Grade"
A3
and enabled if "Washer" shows a number.
Pivot Table lets you do that with a lot less formulas.
Assume your simplified data in "Bolts List" looks like this:

Qty Dia Grade Len Washer WashN
20 0.875 A3 1.75 3 1
3 0.375 A3 1.75 2 1
7 1.000 A3 2.75 2 1
10 0.375 A3 1.00 3 1
14 0.750 A3 1.75 2 1
17 0.625 A2 3.00 3 1
22 0.875 A3 2.50 0
7 0.750 A1 3.00 1 1
19 0.500 A3 2.25 1 1
16 1.000 A2 2.50 3 1
13 0.500 A2 1.50 1 1
9 0.750 A1 2.25 1 1
4 0.500 A1 1.50 2 1
20 0.375 A3 1.75 3 1
1 0.250 A3 0.50 0
3 1.000 A2 3.25 3 1
5 0.625 A1 2.00 1 1
18 0.375 A3 1.50 2 1
6 0.875 A3 2.50 2 1
16 0.250 A3 0.50 2 1

WashN is a helper column with this formula:
=--(ISNUMBER(Washer))
Select all of the above data, including headers, and do Pivot Table.
Layout: ROW = drag in Dia, Len, Grade, WashN in that order
COLUMN = (leave empty)
DATA = Sum of Qty
Options: Uncheck grand totals
Uncheck/Hide Subtotals
From Grade dropdown list, select A3.
From WashN dropdown list, select 1.

Hello Herbert,
Thank you for your reply. This project is an "after hour" project at home
which I spent the better part of the day yesterday without success.
I will work on what you gave me when I get home after work and keep you
posted with my results.

Again thank you so much for your input.
Serge

"Herbert Seidenberg" wrote:

I assume that you want to list in "Summary" all the combinations of
diameters and lengths that appear in "Bolt List", selected by "Grade"
A3
and enabled if "Washer" shows a number.
Pivot Table lets you do that with a lot less formulas.
Assume your simplified data in "Bolts List" looks like this:

Qty Dia Grade Len Washer WashN
20 0.875 A3 1.75 3 1
3 0.375 A3 1.75 2 1
7 1.000 A3 2.75 2 1
10 0.375 A3 1.00 3 1
14 0.750 A3 1.75 2 1
17 0.625 A2 3.00 3 1
22 0.875 A3 2.50 0
7 0.750 A1 3.00 1 1
19 0.500 A3 2.25 1 1
16 1.000 A2 2.50 3 1
13 0.500 A2 1.50 1 1
9 0.750 A1 2.25 1 1
4 0.500 A1 1.50 2 1
20 0.375 A3 1.75 3 1
1 0.250 A3 0.50 0
3 1.000 A2 3.25 3 1
5 0.625 A1 2.00 1 1
18 0.375 A3 1.50 2 1
6 0.875 A3 2.50 2 1
16 0.250 A3 0.50 2 1

WashN is a helper column with this formula:
=--(ISNUMBER(Washer))
Select all of the above data, including headers, and do Pivot Table.
Layout: ROW = drag in Dia, Len, Grade, WashN in that order
COLUMN = (leave empty)
DATA = Sum of Qty
Options: Uncheck grand totals
Uncheck/Hide Subtotals
From Grade dropdown list, select A3.
From WashN dropdown list, select 1.

Hello Herbert,
I have been at my project for over 5 hours this evening with no luck. First
my 63 year old brain is struggling with the pivot table. I have gone back in
my class books & notes about this pivot table. Also I don't understand how
will the pivot table populate A19:B26 in the Summary Sheet.

I'm sorry, I don't get it.

Serge


"Herbert Seidenberg" wrote:

I assume that you want to list in "Summary" all the combinations of
diameters and lengths that appear in "Bolt List", selected by "Grade"
A3
and enabled if "Washer" shows a number.
Pivot Table lets you do that with a lot less formulas.
Assume your simplified data in "Bolts List" looks like this:

Qty Dia Grade Len Washer WashN
20 0.875 A3 1.75 3 1
3 0.375 A3 1.75 2 1
7 1.000 A3 2.75 2 1
10 0.375 A3 1.00 3 1
14 0.750 A3 1.75 2 1
17 0.625 A2 3.00 3 1
22 0.875 A3 2.50 0
7 0.750 A1 3.00 1 1
19 0.500 A3 2.25 1 1
16 1.000 A2 2.50 3 1
13 0.500 A2 1.50 1 1
9 0.750 A1 2.25 1 1
4 0.500 A1 1.50 2 1
20 0.375 A3 1.75 3 1
1 0.250 A3 0.50 0
3 1.000 A2 3.25 3 1
5 0.625 A1 2.00 1 1
18 0.375 A3 1.50 2 1
6 0.875 A3 2.50 2 1
16 0.250 A3 0.50 2 1

WashN is a helper column with this formula:
=--(ISNUMBER(Washer))
Select all of the above data, including headers, and do Pivot Table.
Layout: ROW = drag in Dia, Len, Grade, WashN in that order
COLUMN = (leave empty)
DATA = Sum of Qty
Options: Uncheck grand totals
Uncheck/Hide Subtotals
From Grade dropdown list, select A3.
From WashN dropdown list, select 1.

Help with Pivot Tables see

videos at
DataPig Excel Training - Mike Alexander
http://www.datapigtechnologies.com/ExcelMain.htm

webpages on Pivot Tables
Debra Dalgleish - Contextures.com
http://www.contextures.com/tiptech.html

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Serge" wrote in message ...
Hello Herbert,
I have been at my project for over 5 hours this evening with no luck. First
my 63 year old brain is struggling with the pivot table. I have gone back in
my class books & notes about this pivot table. Also I don't understand how
will the pivot table populate A19:B26 in the Summary Sheet.

I'm sorry, I don't get it.

Serge


"Herbert Seidenberg" wrote:

I assume that you want to list in "Summary" all the combinations of
diameters and lengths that appear in "Bolt List", selected by "Grade"
A3
and enabled if "Washer" shows a number.
Pivot Table lets you do that with a lot less formulas.
Assume your simplified data in "Bolts List" looks like this:

Qty Dia Grade Len Washer WashN
20 0.875 A3 1.75 3 1
3 0.375 A3 1.75 2 1
7 1.000 A3 2.75 2 1
10 0.375 A3 1.00 3 1
14 0.750 A3 1.75 2 1
17 0.625 A2 3.00 3 1
22 0.875 A3 2.50 0
7 0.750 A1 3.00 1 1
19 0.500 A3 2.25 1 1
16 1.000 A2 2.50 3 1
13 0.500 A2 1.50 1 1
9 0.750 A1 2.25 1 1
4 0.500 A1 1.50 2 1
20 0.375 A3 1.75 3 1
1 0.250 A3 0.50 0
3 1.000 A2 3.25 3 1
5 0.625 A1 2.00 1 1
18 0.375 A3 1.50 2 1
6 0.875 A3 2.50 2 1
16 0.250 A3 0.50 2 1

WashN is a helper column with this formula:
=--(ISNUMBER(Washer))
Select all of the above data, including headers, and do Pivot Table.
Layout: ROW = drag in Dia, Len, Grade, WashN in that order
COLUMN = (leave empty)
DATA = Sum of Qty
Options: Uncheck grand totals
Uncheck/Hide Subtotals
From Grade dropdown list, select A3.
From WashN dropdown list, select 1.

Hello Herbert,
I checked out the links provided regarding Pivot Tables. Very interesting
indeed. I'm happy to get these links. Now I will spend some time & get up to
speed with Pivot Tables.

Generally speaking, am I to understand that a Pivot Table could become my
"Summary Sheet".
Can this new workbook containing a "Bolt List" sheet and a Pivot Table
"Summary" sheet be made into a template file.
At the start of a project, what happens to the pivot table when you don't
have any entries in the first sheet?


"Serge" wrote:

Hello Herbert,
Thank you for your reply. This project is an "after hour" project at home
which I spent the better part of the day yesterday without success.
I will work on what you gave me when I get home after work and keep you
posted with my results.

Again thank you so much for your input.
Serge

"Herbert Seidenberg" wrote:

I assume that you want to list in "Summary" all the combinations of
diameters and lengths that appear in "Bolt List", selected by "Grade"
A3
and enabled if "Washer" shows a number.
Pivot Table lets you do that with a lot less formulas.
Assume your simplified data in "Bolts List" looks like this:

Qty Dia Grade Len Washer WashN
20 0.875 A3 1.75 3 1
3 0.375 A3 1.75 2 1
7 1.000 A3 2.75 2 1
10 0.375 A3 1.00 3 1
14 0.750 A3 1.75 2 1
17 0.625 A2 3.00 3 1
22 0.875 A3 2.50 0
7 0.750 A1 3.00 1 1
19 0.500 A3 2.25 1 1
16 1.000 A2 2.50 3 1
13 0.500 A2 1.50 1 1
9 0.750 A1 2.25 1 1
4 0.500 A1 1.50 2 1
20 0.375 A3 1.75 3 1
1 0.250 A3 0.50 0
3 1.000 A2 3.25 3 1
5 0.625 A1 2.00 1 1
18 0.375 A3 1.50 2 1
6 0.875 A3 2.50 2 1
16 0.250 A3 0.50 2 1

WashN is a helper column with this formula:
=--(ISNUMBER(Washer))
Select all of the above data, including headers, and do Pivot Table.
Layout: ROW = drag in Dia, Len, Grade, WashN in that order
COLUMN = (leave empty)
DATA = Sum of Qty
Options: Uncheck grand totals
Uncheck/Hide Subtotals
From Grade dropdown list, select A3.
From WashN dropdown list, select 1.

Thank you David,
I now have your website on my "Favorites". I will make use of all your
information.


"David McRitchie" wrote:

Help with Pivot Tables see

videos at
DataPig Excel Training - Mike Alexander
http://www.datapigtechnologies.com/ExcelMain.htm

webpages on Pivot Tables
Debra Dalgleish - Contextures.com
http://www.contextures.com/tiptech.html

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Serge" wrote in message ...
Hello Herbert,
I have been at my project for over 5 hours this evening with no luck. First
my 63 year old brain is struggling with the pivot table. I have gone back in
my class books & notes about this pivot table. Also I don't understand how
will the pivot table populate A19:B26 in the Summary Sheet.

I'm sorry, I don't get it.

Serge


"Herbert Seidenberg" wrote:

I assume that you want to list in "Summary" all the combinations of
diameters and lengths that appear in "Bolt List", selected by "Grade"
A3
and enabled if "Washer" shows a number.
Pivot Table lets you do that with a lot less formulas.
Assume your simplified data in "Bolts List" looks like this:

Qty Dia Grade Len Washer WashN
20 0.875 A3 1.75 3 1
3 0.375 A3 1.75 2 1
7 1.000 A3 2.75 2 1
10 0.375 A3 1.00 3 1
14 0.750 A3 1.75 2 1
17 0.625 A2 3.00 3 1
22 0.875 A3 2.50 0
7 0.750 A1 3.00 1 1
19 0.500 A3 2.25 1 1
16 1.000 A2 2.50 3 1
13 0.500 A2 1.50 1 1
9 0.750 A1 2.25 1 1
4 0.500 A1 1.50 2 1
20 0.375 A3 1.75 3 1
1 0.250 A3 0.50 0
3 1.000 A2 3.25 3 1
5 0.625 A1 2.00 1 1
18 0.375 A3 1.50 2 1
6 0.875 A3 2.50 2 1
16 0.250 A3 0.50 2 1

WashN is a helper column with this formula:
=--(ISNUMBER(Washer))
Select all of the above data, including headers, and do Pivot Table.
Layout: ROW = drag in Dia, Len, Grade, WashN in that order
COLUMN = (leave empty)
DATA = Sum of Qty
Options: Uncheck grand totals
Uncheck/Hide Subtotals
From Grade dropdown list, select A3.
From WashN dropdown list, select 1.