How do I do this?

[David K]

How to get a calculation or formula to insert its result in another cell?

eg say I want B1 = A1 + 2. The standard way is to put '=A1 + 2' in cell
B1. I want the formula to be elsewhere, not in B1.

Why do I need this?

My spreadsheet records the results in a big round-robin tournament.
There are many players each of whom play several opponents.

Possible scores are 5-0, 5-1 up to 5-4. Say Nick defeats David 5-3.
I want to be able to type just '3' in Nick-vs-David, and have 'Win 5-3'
appear there, and 'Loss 3-5' in David-vs-Nick (cell address known).
This ensures consistency and zero-sum ecven if the score is entered
erroneously.

Currently I am using drop-down lists, and the people entering the
results are making careless errors. There are complex follow-on
calculations which rely on the total being zero-sum, and errors cause
all subsequent calculations to fall over.

 

[Bob Phillips]

You cannot, via a formula, enter a value in another cell, only the cell
containing the formula. But, even if you could, you say the other cell is
unknown, so what would you use?

You can use event code, such that when a value is entered in a cell,
related cells are updated. Using this, you could enter 3 one and it updates
and so does the other, but you need to know where the other is.

 

[David K]

Actually I said that the *address* of the other cell is *known*.

We could define the task as follows:

Upon entering n in Cell A1, where n is an integer, ...
.... Excel does the following:

Updates cell B1 to contain 'nB'.
Updates cell A1 to contain 'nA'. (replacing the originally entered n)

nB and nA are strings eg 7B 7A

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target

.Value = "Win 5-" & .Value
If .Column = 1 Then

.Offset(0, 1).Value = .Value & "Loss " & .Value & "-5"
Else

.Offset(0, -1).Value = .Value & "Loss " & .Value & "-5"
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.