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

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.

--

Debra Dalgleish

Contextures

http://www.yqcomputer.com/

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.

--

Debra Dalgleish

Contextures

http://www.yqcomputer.com/

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

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

n 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.

Serge wrote:

--

Debra Dalgleish

Contextures

http://www.contextures.com/tiptech.html

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.

Serge wrote:

--

Debra Dalgleish

Contextures

http://www.contextures.com/tiptech.html

ood 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

"Debra Dalgleish" wrote:

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

"Debra Dalgleish" wrote:

1. Excel 2002: Any quick way for editing VLOOKUP formulas ?

2. Excel 2002 : Why VLOOKUP formula does not show blanks or N/A ?

3. Excel 2002: Can I convert #N/A to 0 in the VLOOKUP( ) formula ?

4. Are there any improvements in Formula Auditing in Excel 2003 over Excel 2002?

5. Excel>Excel 2002 display formula in another cell as text

6. vlookup formula appearing as formula

7. Link an IF formula with a cell containing a Vlookup formula

8. convert vlookup formula to link formula

9. Alternative formula to the vlookup formula?

10. applying a count formula to a vlookup formula

11. Vlookup formula where lookup value is a result a formula (receive.

12. How do I combine a VLOOKUP formula and a NETWORKDAYS formula?

13. Excel formula doesn't work when put in from vba, but the formula works in Excel

14. Complicated (I think) formula (Excel 2002)

15. Excel 2002 : How to keep =IF( ) formula at stationary position ?

5 post • Page:**1** of **1**