Question About triggering a one time event based on a specific cell value

[dsrm79]

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.

 

[JLGWhiz]

= "b27".value or = Range("$B$27").Value

 

[JLGWhiz]

You probably wanted the whole nine yards. Sorry.

Using VBA

If Range("$B$50") > 0 Then Range("$D$27") = Range("$B$27").Value

Using ws formula in D27

= IF(b50>0,b27,"")

 

[Tushar Mehta]

From what you've described you should be able to simplify your worksheet and
improve its auditability.

Leave the base amount in B50 alone. This immediately provides any one
looking at the worksheet information about the base amount.

Since B27 already has the sum of all sales, it gives at a glance information
about the total sales.

So, in D27, all you need is a formula =if(b27>=b50,b27,0)

No events and no programming needed.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions