How to update the contents of one cell based on the contents inputted inside another cell?

Question

I want a cell to automatically update when the contents in another cell are changed so that the data in both the cells fit a function. For example I have some data in B2 and some other data in B3, I want there to be a kind of restrain of B2+B3 = 29. Suppose that B2 has a data of value x and B3 has a data of value y,so if I change B1 from say x to x+7, then the data in B2 should go from y to y-7.

Answer 1

You can make this constraint easily if you only need to input into B2 and never manually write into B3:

	A	B	C
1
2		20
3		=29-B2

If you need to manually enter values into both B2 and B3 you could set up a validating function:

	A	B	C
1
2		20
3		9
4		=If(B2+B3=29,"","B2 and B3 must sum to 29!")

You could also set up a conditional formatting rule to highlight invalid selections.

To automatically enter the other value to keep the total 29, you'd need VBA.