Two-Way Calculatoin

[Guest]

For example, if Y = X*2
X is in cell A1
Y is in cell A2
we usually write "= A1*2" in A2 " to find out the Y value when we know X
or we could write "= A2/2" in A1 to calculate X when we have Y
but these functions work one direction only
what if I sometimes know X and some other times know Y

How do I write a two-way function where I can put in known X value or Y
value to calculate for the other unknown value (Y or X)

 

[Guest]

Write a user defined function (in VBA).
and use in in both cells A1 and A2
VBA can not create circular references.

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const cdScale As Double = 2
With Target
If Not Intersect(Range("A1:A2"), Cells) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
If .Address = "A1" Then
.Offset(1, 0).Value = .Value * cdScale
Else
.Offset(-1, 0).Value = .Value / cdScale
End If
Application.EnableEvents = True
End If
End With
End Sub

 

[Dave Peterson]

Just a couple of typo corrections (.cells and $A$1):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const cdScale As Double = 2
With Target
If Not Intersect(Range("A1:A2"), .Cells) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
If .Address = "$A$1" Then
.Offset(1, 0).Value = .Value * cdScale
Else
.Offset(-1, 0).Value = .Value / cdScale
End If
Application.EnableEvents = True
End If
End With
End Sub

 

[JE McGimpsey]

Just a couple of typo corrections (.cells and $A$1):

Thanks Dave! forgot to copy the corrected routine from my test book.

 

[Guest]

Thank you so much!!

Thanks Dave! forgot to copy the corrected routine from my test book.

 

[Guest]

One more question -

What textbooks would you recommend for further readings on similar excel
codings to what you just showed me? I really want to learn more about it.

 

[Dave Peterson]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.