Question About triggering an event based on a specific cell value 1

[mmiram]

Hi all,

I am trying to create a sales commission calculator for all the sales people in our office.

Just before I get into my exact problem, I will try and explain how the system works. The sales people get a certain base salary. They sell different products all of which have different profit margins. Every month, their commission does not kick in until they sell enough to
cover a certain "base amount" that is set for that month.

Here is where I am stuck. I can calculate the commissions on their sales without any issues. I have a cell (B50) that contains a default value of a negative number that equals the "base amount" for that month. This cell (B50) is a calculated field that is based on a percentage of their total sales for that month. Everytime, the sales person makes a sale and plugs it into the spreadsheet, the negative
number keeps getting reduced. The idea is as soon as the the cell hits "0", their commision kicks in as they covered their base amount for that month. I also have another cell (B27) that keeps a track of their total sales amount as they keep plugging their sales data into the spreadsheet.

What I need is an event that monitors B50. As soon as B50, becomes a positive number (>0), it should take the amount in cell B27 (the total sales at that point) and copy the value to a different cell (D27). What I need in D27 is the actual value of B27 and not the formula. This is required for me as I need the dollar amount at which the commission kicks in to calculate their total commission.

How can I do that? Is it even possible in excel.

Thanks for any help and please let me know if you need any further clarifications.

Thanks.

 

[SkipVought]

Hi,

Isn't that just a formula?
[tt]
D27: =if(B50>0,B27,0)
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[mmiram]

No, the reason it cannot be a formula is, I want the amount that is contained in B27, the very first time, the negative amount becomes positive in B50. B50 keeps changing even after it goes positive. I do not want the value in D27 to keep changing everytime B50 changes. The event should be triggered only once, the first time B50 becomes positive.

Thanks.

 

[SkipVought]

Does anything "RESET" the process?
Paste this Event Process in the SHEET OBJECT where these cells reside...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = [B50].Address Then
        If Target.Value > 0 Then
            If [D27].Value = "" Then
                [D27].Value = [B27].Value
            End If
        End If
    End If
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[CBasicAsslember]

Let me see if I have this right. For the sake of argument assume the "Base Amount" is $100.00 and the percentage is 10%. The formula in B50 would be:

= ('Sum of Sales' * 10%) - 100

In this case, when the 'Sum of Sales' is $1000.00, the equation would evaluate to 0 and the commission would kick in for any further sales. If this is the case I would create a seperate cell and name it 'Base_Amount'. The equation in B50 would then be:

= ('Sum of Sales' * 10%) - 'Base_Amount'

Since you already know the 'Base_Amount' and the percentage Cell D27 would then be:

= If (B50>0,Base_Amount/10%,"")

 

[mmiram]

Hey Skip,

That worked great, thanks. CBasicAsslembler, the reason I could not do what you suggested is because the percentage varies for different products and different quantities...it is not fixed.

Thanks for all your help guys.