If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUM determined by drop-down list
HI, I have a product list with different products in each row,
Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-wrighting the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for "E" for each row based on the option selected from the drop down. Thsi is what I am trying to do, (If D5=L8, then M5*C5*M*, or If D5=L9, then M5*C5*M10) ans so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S |
#2
|
|||
|
|||
SUM determined by drop-down list
Correction to last sentence:
Thsi is what I am trying to do, (If D5=L8, then M5*C5*M8, or If D5=L9, then M5*C5*M9, or if D5=L10, then M5*C5*M10) and so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S Note Items is L8-L13 are Text descriptions Items in M8-M13 are a decimal value used as a multiplier eg, 1.2, 1.7, 2.1 etc associated with the option in L. "RICH1B" wrote: HI, I have a product list with different products in each row, Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-wrighting the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for "E" for each row based on the option selected from the drop down. Thsi is what I am trying to do, (If D5=L8, then M5*C5*M*, or If D5=L9, then M5*C5*M10) ans so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S |
#3
|
|||
|
|||
SUM determined by drop-down list
I'm unclear if you stated your case correctly...?
If D5=L8, then M5*C5*M*, M5*C5*M???*??? or If D5=L9, then M5*C5*M10) and here do you mean M5*C5*M9 instead of *M10? Taking a semi wild guess try this in E2 and pull down: =$M$5*C2*M8 Where your headers are in row 1 and data is in A2 to D6. HTH Regards, Howard "RICH1B" wrote in message ... HI, I have a product list with different products in each row, Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-wrighting the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for "E" for each row based on the option selected from the drop down. Thsi is what I am trying to do, (If D5=L8, then M5*C5*M*, or If D5=L9, then M5*C5*M10) ans so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S |
#4
|
|||
|
|||
SUM determined by drop-down list
Having read your corrections to your original post after I posted a possible
solution, I would say my suggestion will not do what you are asking. Back to the drawing boards for me with the new and corrected info. Regards, Howard "RICH1B" wrote in message ... HI, I have a product list with different products in each row, Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-wrighting the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for "E" for each row based on the option selected from the drop down. Thsi is what I am trying to do, (If D5=L8, then M5*C5*M*, or If D5=L9, then M5*C5*M10) ans so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S |
#5
|
|||
|
|||
SUM determined by drop-down list
Thanks I corrected the last sentence in the 2nd post. or here for easier
reading. I have a product list with different products in each row, Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-writing the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for "E" for each row based on the option selected from the drop down. This is what I am trying to do, (If D5=L8, then M5*C5*M8, or If D5=L9, then M5*C5*M9, or if D5=L10, then M5*C5*M10) and so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S Note Items is L8-L13 are Text descriptions Items in M8-M13 are a decimal value used as a multiplier e.g., 1.2, 1.7, 2.1 etc associated with the option in L. "L. Howard Kittle" wrote: I'm unclear if you stated your case correctly...? If D5=L8, then M5*C5*M*, M5*C5*M???*??? or If D5=L9, then M5*C5*M10) and here do you mean M5*C5*M9 instead of *M10? Taking a semi wild guess try this in E2 and pull down: =$M$5*C2*M8 Where your headers are in row 1 and data is in A2 to D6. HTH Regards, Howard "RICH1B" wrote in message ... HI, I have a product list with different products in each row, Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-wrighting the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for "E" for each row based on the option selected from the drop down. Thsi is what I am trying to do, (If D5=L8, then M5*C5*M*, or If D5=L9, then M5*C5*M10) ans so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S . |
#6
|
|||
|
|||
SUM determined by drop-down list
Thanks, looking forward to something that works "L. Howard Kittle" wrote: Having read your corrections to your original post after I posted a possible solution, I would say my suggestion will not do what you are asking. Back to the drawing boards for me with the new and corrected info. Regards, Howard "RICH1B" wrote in message ... HI, I have a product list with different products in each row, Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-wrighting the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for "E" for each row based on the option selected from the drop down. Thsi is what I am trying to do, (If D5=L8, then M5*C5*M*, or If D5=L9, then M5*C5*M10) ans so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S . |
#8
|
|||
|
|||
SUM determined by drop-down list
I tried to send, but email returned and failed.
"L. Howard Kittle" wrote: If you want send me a sample workbook. Do not include priority info, rather an example of your worksheet using dummy info and details on the results you expect beyond what you have posted so far. Regards, Howard "RICH1B" wrote in message ... HI, I have a product list with different products in each row, Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-wrighting the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for "E" for each row based on the option selected from the drop down. Thsi is what I am trying to do, (If D5=L8, then M5*C5*M*, or If D5=L9, then M5*C5*M10) ans so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S . |
#9
|
|||
|
|||
SUM determined by drop-down list
I did get the attachment but was unable to open it. Perhaps because of the
..xlsx Said I did not have a program to run the file. Sorry. Regards, Howard "L. Howard Kittle" wrote in message ... If you want send me a sample workbook. Do not include priority info, rather an example of your worksheet using dummy info and details on the results you expect beyond what you have posted so far. Regards, Howard "RICH1B" wrote in message ... HI, I have a product list with different products in each row, Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-wrighting the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for "E" for each row based on the option selected from the drop down. Thsi is what I am trying to do, (If D5=L8, then M5*C5*M*, or If D5=L9, then M5*C5*M10) ans so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S |
#10
|
|||
|
|||
SUM determined by drop-down list
For what it's worth, I offered these two solutions to OP's query, both seem
to be working okay. A worksheet formula: =IF(ISNA(LOOKUP(D5,$L$8:$L$11,$M$8:$M$11)*$M$5*C5) ,"",LOOKUP(D5,$L$8:$L$11,$M$8:$M$11)*$M$5*C5) And a change_event macro. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column 4 Then Exit Sub Dim i As Variant Dim j As Range Dim k As Long Dim l As Long i = ActiveCell.Value k = ActiveCell.Offset(0, -1).Value l = Range("M5").Value Set j = Range("L8:M11") i = ActiveCell.Application.WorksheetFunction _ .VLookup(i, j, 2, False) ActiveCell.Offset(0, 1).Value = i * k * l End Sub Regards, Howard "RICH1B" wrote in message ... HI, I have a product list with different products in each row, Column A is the number, Column B is the Description, Column C is the Quantity, Column D is a drop down list of options, linked to L8-L13 (list holder cells) created with the Data Verification function, and M8-M13 is the price multiplier matched to the option. I have M5 as the base price holder cell (so I can change the base without re-wrighting the entire formula) Column E is the Total for the Row, based on the Option selected from D Drop-down list linked to L. I am having trouble creating the formula for "E" for each row based on the option selected from the drop down. Thsi is what I am trying to do, (If D5=L8, then M5*C5*M*, or If D5=L9, then M5*C5*M10) ans so on down the list of options. Each time I try I get a failed function or a #NAME? error. I have tried SUMIF, SUMIF S |
Thread Tools | |
Display Modes | |
|
|