Limit sum of values in range

[JackDanIce]

Hi

I have the following table and related formula's:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Lion start date:	07/06/2010
2
3	Free man days per year:	15
4	Per Month:	1.25
5
6		2010-2011
7	Month	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May
8	Days brought forward	0	1.25	2.5	3.75	5	6.25	7.5	8.75	10	11.25	12.5	13.75
9	Free days accrued	1.25	1.25	1.25	1.25	1.25	1.25	1.25	1.25	1.25	1.25	1.25	1.25
10	Days used												2
11	Days stripped out	0	0	0	0	0	0	0	0	0	0	0	0
12	Days carried forward	1.25	2.5	3.75	5	6.25	7.5	8.75	10	11.25	12.5	13.75	13
13
14	Total number of days used	-	-	-	-	-	-	-	-	-	-	-	2
15	(On a rolling 12 month basis)
16												Total used in 2010-2011:	2
17													Unused/Overused:	13
Sheet1

How can I limit the sum of the yellow cells in row 10 to never exceed 15 (value in B3) would it be with Data->Validation? The values in row 10 will always be positive and I don't want a macro or worksheet event change solution.

Many thanks,
Jack

 

[JackDanIce]

Not to worry, found the answer here:
http://www.contextures.com/xlDataVal07.html

 

[number kruncher]

Jack,

If you do use Data/Validation, then set the Allow to Decimal, Data to Between, Min as ZERO and Max as Opening balance in that month.

Then you could use Conditional Formatting to limit the Yellow on Row 10, to those that only have a value in that months opening balance

If you allow the accrued amount in the month, to be used in the month, then ADD this to the Opening Balance above.