Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT search versus other method
I have this program created with the help of many smart people in the user
group & many thanks to all that help. I have a bolt list sheet and a summary sheet with "sumproduct" formulas that retreive from the "bolt list" sheet, quantities of different bolt diameter and length. But in order for the summary sheet to work, I must input: diameter & length in the appropreate cell for a specific grade of bolt. In other word the, "summary" sheet retreives from the "bolt list" sheet and I would like the in formation from "bolt list" sheet send to the "summary" sheet without any input from the user. Is this request logicaly possible? If so, would someone guide me in acheiving this method please. Many Thanks Serge |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT search versus other method
Put all the diameters in A2 down, all lengths in B1 across, and put the SP
formula in B2 =SUMPRODUCT(--(Sheet1!$A$1:$A$1000=$A2),--($B$A:$B$1000=b$1)) adjust the formula to match your data, then copy down and across -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Serge" wrote in message ... I have this program created with the help of many smart people in the user group & many thanks to all that help. I have a bolt list sheet and a summary sheet with "sumproduct" formulas that retreive from the "bolt list" sheet, quantities of different bolt diameter and length. But in order for the summary sheet to work, I must input: diameter & length in the appropreate cell for a specific grade of bolt. In other word the, "summary" sheet retreives from the "bolt list" sheet and I would like the in formation from "bolt list" sheet send to the "summary" sheet without any input from the user. Is this request logicaly possible? If so, would someone guide me in acheiving this method please. Many Thanks Serge |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT search versus other method
Hello Bob,
Thanks for your reply. You have help me in the past. =SUMPRODUCT(('Bolt List'!$D$9:$D$188=A8)*('Bolt List'!$J$9:$J$188=B8)*('Bolt List'!$E$9:$E$188="A325 Black")*(ISNUMBER('Bolt List'!$G$9:$G$188)) *'Bolt List'!$C$9:$C$188) The formula above applies to one the the bolt grades I work with. I have been digesting your reply and I'm a little puzzled. In columns: C=quantities, D=diameters, E=grades, J=lentgths In the summary sheet I have 6 areas corresponding to 3 grades of bolts and each grade are either galvanized, balck or plated. The above formula is modified for each area. Currently as I input information in the "Bolt List" sheet which gives me the length by input of the amount of grip that the connection requires, once I get this length I go to the "summary" sheet and input that length and the diameter then I retreive the quantity from the "bolt List" sheet. If while in the "Bolt List" sheet in the next row the same diameter, grade & length are required for a different connection, the quantity in the "summary" sheet keeps updating and so on. The user needs to remember when filling out the "Bolt List" sheet that if a different length for same diameter or same length with different diameter, the user needs to go to the "Summary" sheet and input tthat information in the next row and so on. I wish to eliminate this potential disaster. Any help is greatly appreciated. Serge "Bob Phillips" wrote: Put all the diameters in A2 down, all lengths in B1 across, and put the SP formula in B2 =SUMPRODUCT(--(Sheet1!$A$1:$A$1000=$A2),--($B$A:$B$1000=b$1)) adjust the formula to match your data, then copy down and across -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Serge" wrote in message ... I have this program created with the help of many smart people in the user group & many thanks to all that help. I have a bolt list sheet and a summary sheet with "sumproduct" formulas that retreive from the "bolt list" sheet, quantities of different bolt diameter and length. But in order for the summary sheet to work, I must input: diameter & length in the appropreate cell for a specific grade of bolt. In other word the, "summary" sheet retreives from the "bolt list" sheet and I would like the in formation from "bolt list" sheet send to the "summary" sheet without any input from the user. Is this request logicaly possible? If so, would someone guide me in acheiving this method please. Many Thanks Serge |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT search versus other method
Serge,
Can you send me a sample spreadsheet, and I will knock up something that should work for you? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Serge" wrote in message ... Hello Bob, Thanks for your reply. You have help me in the past. =SUMPRODUCT(('Bolt List'!$D$9:$D$188=A8)*('Bolt List'!$J$9:$J$188=B8)*('Bolt List'!$E$9:$E$188="A325 Black")*(ISNUMBER('Bolt List'!$G$9:$G$188)) *'Bolt List'!$C$9:$C$188) The formula above applies to one the the bolt grades I work with. I have been digesting your reply and I'm a little puzzled. In columns: C=quantities, D=diameters, E=grades, J=lentgths In the summary sheet I have 6 areas corresponding to 3 grades of bolts and each grade are either galvanized, balck or plated. The above formula is modified for each area. Currently as I input information in the "Bolt List" sheet which gives me the length by input of the amount of grip that the connection requires, once I get this length I go to the "summary" sheet and input that length and the diameter then I retreive the quantity from the "bolt List" sheet. If while in the "Bolt List" sheet in the next row the same diameter, grade & length are required for a different connection, the quantity in the "summary" sheet keeps updating and so on. The user needs to remember when filling out the "Bolt List" sheet that if a different length for same diameter or same length with different diameter, the user needs to go to the "Summary" sheet and input tthat information in the next row and so on. I wish to eliminate this potential disaster. Any help is greatly appreciated. Serge "Bob Phillips" wrote: Put all the diameters in A2 down, all lengths in B1 across, and put the SP formula in B2 =SUMPRODUCT(--(Sheet1!$A$1:$A$1000=$A2),--($B$A:$B$1000=b$1)) adjust the formula to match your data, then copy down and across -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Serge" wrote in message ... I have this program created with the help of many smart people in the user group & many thanks to all that help. I have a bolt list sheet and a summary sheet with "sumproduct" formulas that retreive from the "bolt list" sheet, quantities of different bolt diameter and length. But in order for the summary sheet to work, I must input: diameter & length in the appropreate cell for a specific grade of bolt. In other word the, "summary" sheet retreives from the "bolt list" sheet and I would like the in formation from "bolt list" sheet send to the "summary" sheet without any input from the user. Is this request logicaly possible? If so, would someone guide me in acheiving this method please. Many Thanks Serge |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search /Filter vertical Numeric pattern (down single column) | Excel Worksheet Functions | |||
CAN YOU DYNAMICALY CHANGE VLOOKUP SEARCH RANGE? | Excel Discussion (Misc queries) | |||
Search within workbook | New Users to Excel | |||
Edit macro to match entire cell contents | Excel Discussion (Misc queries) | |||
Wildcard search functions within Vlookup | Excel Worksheet Functions |