Hi Snake,
You can do this sort of thing using a 'Worksheet_SelectionChange' macro, which you place in the relevant worksheet's object. To do
this, open the VBA Editor's Project Explorer window (eg Alt-F11), then double-click the entry for the worksheet you're using. Insert
following lines in the code window:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static oCell As Range
If oCell Is Nothing Then Set oCell = ActiveCell
If Intersect(oCell, ActiveSheet.Range("A2:C2")) Is Nothing Then GoTo Reset
With ActiveSheet
Select Case oCell
Case .Range("A2")
.Range("B2").Value = .Range("A2").Value * 1.5
.Range("C2").Value = .Range("A2").Value * 2.5
Case .Range("B2")
.Range("A2").Value = .Range("B2").Value / 1.5
.Range("C2").Value = .Range("B2").Value / 1.5 * 2.5
Case .Range("C2")
.Range("A2").Value = .Range("C2").Value / 2.5
.Range("B2").Value = .Range("C2").Value / 2.5 * 1.5
End Select
End With
Reset:
Set oCell = ActiveCell
End Sub
The above procedure reacts to changes in A2:C2 according to whichever cell in that range was the last updated. It's a fairly trivial
example, just to give you an idea of where to start. If you change the value in any one of these three cells, the other two get
updated. The formulae in the code ensure the same mathematical relatioinship is maintained across all three cells.
Note: In the above macro, ‘Target’, although required for a ‘SelectionChange’ event, isn’t used since it would prevent the target
cells updating if the value in one of them is changed and a cell outside the target range is immediately activated without pressing
the <Enter> key.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------
Snake007 said:
The conditional statement will produce an error if data is entered into more
than one of the 3 cells. The user will be able to enter data into any one of
the 3 and get results in the other 2. Keep in mind that I am using this an an
exsample, the actual sheet will be a lot more than 3 cells.
macropod said:
And if the user enters data in B1, or any two of A1, B1 & C1?
--
macropod
[MVP - Microsoft Word]
-------------------------
Snake007 said:
I would like to use a conditional statement and based on the condition move
the result of a formula to a blank cell.
Example: cells A1 , B1 , C1 are blank. If a person enters data in A1 a math
fuction is done and results are displayed in B1 and C1. However if the data
is entered in C1 a different math fuction is done and the results are
displayed A1 and B1.
Is this possible?
