Automatically Change Cell Value

Hi,

I am tryinto to plan a schedule and need excel to automatical xfer the value to another cell if the total for the day has exceed.
e.g if a set the limit to be 15 everyday how can i make sure that on mon the 3 if shifted to tueasday and 14 is shifted to wed automatically.

Mon Tue Wed Thur
1 3
2 10 29
3 5
tried using if function but can't seem to nest the thing propely
Thanks

I'm confused. If 3 is shifted to Tuesday, wouldn't that make Tuesday's total 32, meaning that 15 is transferred to Wednesday and 2 to Thursday?

yes that is rite. things will automatically be xfered such that the total for the day would only be 15 max.

Assuming your table is in A1:E4, B5 could have =MIN(SUM(B2:B4),15) and C5 could have =MIN(SUM($B$2:C4)-SUM($B$5:B5),15), dragged over for D5 and E5.

thks. but this method will always ensure that the last column is fixed to 15. but i would want the value from A2 to E4 to change accordingly.

The only way to have it change automatically is with a macro. Unfortunately, I've never made time to study VBA, so I can't really help. Maybe one of the other gurus can.