Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 VLOOKUP formula or other formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 VLOOKUP formula or other formula
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. Serge wrote: 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 Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 VLOOKUP formula or other formula
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" wrote: 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. Serge wrote: 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 Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 VLOOKUP formula or other formula
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. Serge wrote: 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" wrote: 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. Serge wrote: 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 Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 VLOOKUP formula or other formula
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 "Debra Dalgleish" wrote: 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. Serge wrote: 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" wrote: 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. Serge wrote: 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 Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 : Why VLOOKUP formula does not show blanks or N/A ? | Excel Discussion (Misc queries) | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Vlookup formula - Excel Version 2002 | Excel Worksheet Functions | |||
Vlookup formula Excel version 2002 | Excel Discussion (Misc queries) | |||
VLookup Formula Excel Version 2002 | Excel Worksheet Functions |