What type of formula?

[Guest]

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?

 

[Herbert Seidenberg]

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

 

[Guest]

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

 

[Guest]

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

 

[David McRitchie]

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

 

[Guest]

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?

 

[Guest]

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