Is it possible to "link" two cells in Excel via some sort of formula?

Question

For example, I want the contents of A1 and B1 to always add up to 1000. If the user types "200" into cell A1, I want B1 to automatically update to be "800". Likewise, if the user types "600" into cell B1, I want A1 to automatically update to be "400".

I can do one or the other pretty easily—I can set A1 to be "=1000 - B1" for example, and get the second half of the behavior I want. But if the user types "200" into A1, it overwrites my formula and now I'm left with numbers that don't match and don't auto-update.

Is what I'm after possible? Or is Excel the wrong tool here?

Answer 1

You need a workaround.

Have 2 cells to have user entered values;

And then use another 2 cells to display values, So that way, you can use

if(isblank($A$1),'',2000-$A$1)
if(isblank($B$1),'',2000-$B$1)

in the other cells.

Answer 2

See my answer to a similar question asked recently.

http://stackoverflow.com/questions/2138698/excel-2-sheets-2-columns-same-value/2140243#2140243