Cross reference

  • Thread starter Thread starter Edward
  • Start date Start date
E

Edward

a simple excel, I have 4 columns in my excel

A is for checking
B is the price or product
C is profit
D is cost

what i want to do is simple, I set formular in C & D, once a user
input either C or D, the another cell will show the different. i.e.

if C is input, D=B-C
if D is input, C=B-D

then A is checking purpose, if either C or D is inputted, it will
shown "Y"

but if I use IF(D>0, B-D, "") it will introduce cyclic reference.

any solution for this?
Thanks.
 
hi, Edward !

AFAIK, you can't "fool" excel's calculation engine (tracking precedents & dependents)

*IF* the use of code (vba) is a valid alternate, try the following:

- right-click on "that" tab (sheet-name) and choose "view code"
- copy/paste (or write) the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("c:d")) Is Nothing Then ActiveCell.Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("c:d")) Is Nothing Then Exit Sub
Dim Move As Integer: On Error GoTo Finish
Application.EnableEvents = False
With ActiveCell: Move = IIf(.Column = 3, 1, -1)
.Offset(, Move).Formula = "=b" & .Row & "-" & .Address(0, 0)
End With
Finish:
Application.EnableEvents = True
End Sub

if any doubts (or further information)... would you please comment ?
hth,
hector.

__ OP __
 
Back
Top