A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SUM determined by drop-down list



 
 
Thread Tools Display Modes
  #1  
Old April 16th, 2010, 11:22 PM posted to microsoft.public.excel.worksheet.functions
RICH1B
external usenet poster
 
Posts: 5
Default 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  
Old April 16th, 2010, 11:33 PM posted to microsoft.public.excel.worksheet.functions
RICH1B
external usenet poster
 
Posts: 5
Default 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  
Old April 16th, 2010, 11:52 PM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default 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  
Old April 17th, 2010, 12:18 AM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default 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  
Old April 17th, 2010, 12:56 AM posted to microsoft.public.excel.worksheet.functions
RICH1B
external usenet poster
 
Posts: 5
Default 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  
Old April 17th, 2010, 01:48 AM posted to microsoft.public.excel.worksheet.functions
RICH1B
external usenet poster
 
Posts: 5
Default 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



.

  #9  
Old April 17th, 2010, 03:46 PM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default 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  
Old April 19th, 2010, 10:13 PM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.