Don't know which formula to use!

[JHC]

If I have several sheets in a workbook, and I want one as the master to refer to all others so that when a number is placed in a cell on the master it is either subtracted or added from the same cell in another sheet, depending which sheet or sheets are named from the master sheet. Is this possible?

 

[JE McGimpsey]

Could you explain what you mean in a bit more detail?

For instance, will the entry on the Master sheet be made in the same
cell each time? If not, will it be the same cell for each named sheet?

How will the name in the master sheet correspond to the value- to the
left? right? above? below? How will multiple sheets be designated?

Do you want this to happen when a value entry is made? when a name is
entered? when a button is clicked?

What determines whether the number is "subtracted or added from"?

 

[JHC]

A list of items on all the sheets, including the master sheet, will be the same, in the same rows on each sheet. When I type in a number on the master sheet, I want it to add to the sheet where the item is being sent, and subtracted from the sheet where the item is coming from respectively. It is a basic inventory that has many differant areas where supplies are coming from, and many differant areas where the same items are being sent.

 

[JE McGimpsey]

I'm sure this makes sense to you, but I'm still fuzzy.

How does XL know what "sheet where the item is being sent" or "wheret he
item is coming from"?

For instance, if the item is in column A, quantity in column B, the
"from" in column C, and the "to" in column D, one way would be to put
this in the worksheet code module (right-click on the worksheet tab and
choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 The Exit Sub
If .Column = 2 Then
With Sheets(.Offset(0, 1)).Range(.Address)
.Value = .Value - Target.Value
End With
With Sheets(.Offset(0, 2)).Range(.Address)
.Value = .Value + Target.Value
End With
End If
End With
End Sub

But there are a lot of other factors: Are you sure you want this to
happen automatically? There's no history, so any mistakes will be
difficult to reverse.

This assumes that the entry should be made in column 2 of the from and
to sheets.

This will fire every time the quantity changes, so the from and to have
to be filled in first - is that acceptable?

What should happen if the from or to is misentered?