Create 3 Drop-Down Lists which Sum to Specific Value?

[#VALUE]

Hi all,

I have an excel question which I've been scratching my head about...

I've had a few attempts at solving this from butchering various bits of VBA I've found, but although it works (still with a few bugs to be ironed out), I'm confident there's a better way of doing this.

So, here goes:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Attribute 1[/TD]
[TD][Yes/No][/TD]
[TD][Total_Count][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][Value_1][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][Value_2][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][Value_3][/TD]
[/TR]
</tbody>[/TABLE]

1) "Attribute 1" is just plain text; for example "Split of Total figures"
2) "Yes/no" is an enabling field using a static Yes/No data validation list
3) If you set "Yes/No" to "Yes", "Total_Count" field should be open for population (bonus points if this cell is locked if a user chooses "No")
4) Once "Total_Count" is populated with N, "Value_1", "Value_2" and "Value_3" are populated with drop down lists which display "1-N" as available options. When all of these cells are populated, they should sum to the value "Total_Count"
5) If I populate "Yes", and "Total_Count" with 20, then imagine I pick "Value_2" and set it to "10" from the dropdown list - I would like it if the dropdown lists in "Value_1" and "Value_2" contracted so that they only displayed the numbers "1-10", rather than "1-20"...
6) Finally, if I change the "Total_Count" to empty/zero, I'd like the contents of Value_1, Value_2 and Value_3 to be cleared. If I change the "Yes/No" to "No", I'd like it if the "Total_Count" was cleared automatically (as well as the values below it).


I'd be grateful for any help toward solving this!