SUM determined by drop-down list

R

RICH1B

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
 
R

RICH1B

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

L. Howard Kittle

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
 
L

L. Howard Kittle

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
 
R

RICH1B

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

RICH1B

Thanks, looking forward to something that works
L. Howard Kittle said:
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




.
Click to expand...
 
L

L. Howard Kittle

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.

[email protected]

Regards,
Howard
 
R

RICH1B

I tried to send, but email returned and failed.

L. Howard Kittle said:
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.

[email protected]

Regards,
Howard




.
Click to expand...
 
L

L. Howard Kittle

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

L. Howard Kittle

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
 
Top