Update List Number Going Forward in a Column Once Other Cell Is Greater Than A Specific $ Number

[bear1970]

Hello! This one has me wrapped around the axle for sure!

I'm creating a sales person's commission calculator. New sales people will start at a specific percent commission (currently showing 72% in column E) I have a list on the right of available commission percentages. This list is available as a drop down in column E. However, when the companies amount in cell H13 hits $19,808 and higher their commission will change to 92% going forward. So I'd like the amount in column E to change at the point and row that H13 hits or goes above the $19,808 as each sale is entered.

Example: So let's say sales person Mary starts the years out at 76% in column "Sales person's split %". The company will enter in 76% in column E going down. Mary sells enough for the "Company $" total to reach or exceed $19,808 in whatever row so that and subsequent rows in column E all change to 92%.

I've attached the sheet below. I do so appreciate any and all help!!

Thank you,
-Barry

Agent Commission.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		List price	Commision	GCI	Sales person's split %	Company split %	Sales person $	Company $					List
3	1	$500,000.00	3%	15,000.00	72	28	$10,800.00	$4,200.00					72
4	2				72								76
5	3				72								80
6	4				72								84
7	5				72								88
8	6				72								92
9	7				72
10	8				72
11	9				72
12
13		Totals					$10,800.00	$4,200.00
14
Sheet1

Cell Formulas
Range	Formula
D3	D3	=B3*C3
F3	F3	=100-E3
G3	G3	=D3*E3%
H3	H3	=D3*F3%
G13:H13	G13	=SUM(G3:G11)

Cells with Data Validation
Cell	Allow	Criteria
E3:E11	List	=$M$3:$M$8

 

[MARK858]

What to do?

Have you set up the named ranges?