Putting Formula in all cells give Circular Reference. Please help

[K]

I have figures in cell A1 , B1 & C1 (see below)
A B C…col
2.0 8.7 5.4%

in cell A1 I have nothing , in cell B1 I have formula "=A1*52/12" and
in cell C1 again I have formula "=(B1*12)/(37*52)"

As I got formulas in cell B1 & C1 so when ever I put any figure in
cell A1, I get figures automatically appearing in other cells as shown
above if I put 2.0 in cell A1 then I get 8.7 in B1 and 5.4% in C1. I
want to put formula in cell A1 as well and want to chane formulas in
cells B1 and C1 little bit so like this i'll have formulas in all
three cells. I want this because lets say if user put 8.7 in cell B1
then i want 2.0 to automatically appear in cell A1 and 5.4% to in C1.
In other words if user put figure any one of three cells then i want
remaing cells to produce result automatically. I tried putting
formulas in all three cells put i get circular reference error. I
know i can get rid of by ticking box of iterative calculation in
options but then i'll get different results. Is there any way that i
can have formulas in all three cells and if any one of the cells value
get change manually then remaing two show the correct results. and i
want this without having circular reference error. Please can any
friend can help

 

[Sam Wilson]

1. Press Alt + F11 to show the VBE window
2. In the top left pane (Project - VBAProject, find the sheet you're working
on, right-click it and view code
3. In the main window, change (General) to Worksheet and in the box to the
right pick Change. You should see:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

In between these two lines, paste the following:


On Error GoTo errorcatcher

Application.EnableEvents = False

If Not Intersect(Target, Columns("A:A")) Is Nothing Then
Target.Offset(0, 1).Formula = "=" & Target.Address(False, False) &
"*52/12"
Target.Offset(0, 2).Formula = "=" & Target.Offset(0, 1).Address(False,
False) & "*12/(52*37)"
End If

If Not Intersect(Target, Columns("B:B")) Is Nothing Then
Target.Offset(0, -1).Formula = "=" & Target.Address(False, False) &
"*12/52"
Target.Offset(0, 1).Formula = "=" & Target.Address(False, False) &
"*12/(52*37)"
End If

If Not Intersect(Target, Columns("C:C")) Is Nothing Then
Target.Offset(0, -1).Formula = "=" & Target.Address(False, False) &
"*(37*52)/12"
Target.Offset(0, -2).Formula = "=" & Target.Offset(0, -1).Address(False,
False) & "*12/52"
End If

errorcatcher:
Application.EnableEvents = True

 

[Rick Rothstein]

Just guessing here, but I assume you want this functionality for all the
rows in Columns A through C. You can do that using VB event code. Right
click the tab at the bottom of the worksheet you want this functionality on
and select View Code from the pop up window that appears, then Copy/Paste
the following code into the code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:C")) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
Select Case Target.Column
Case 1
Target.Offset(, 1) = Target * 52 / 12
Target.Offset(, 2) = Target / 37
Case 2
Target.Offset(, -1) = Target * 12 / 52
Target.Offset(, 1) = Target * 12 / (37 * 52)
Case 3
Target.Offset(, -2) = 37 * Target
Target.Offset(, -1) = 37 * 52 * Target / 12
End Select
End If
Whoops:
Application.EnableEvents = True
End Sub

Now, go back to the worksheet and type a number into either Column A, B or C
and watch the other columns fill in automatically. By the way, I presume
that you have the cells in those columns formatted the way you want (one
decimal place in Columns A and B and one decimal place percentage in Column
C as your example data shows).

--
Rick (MVP - Excel)


I have figures in cell A1 , B1 & C1 (see below)
A B C…col
2.0 8.7 5.4%

in cell A1 I have nothing , in cell B1 I have formula "=A1*52/12" and
in cell C1 again I have formula "=(B1*12)/(37*52)"

As I got formulas in cell B1 & C1 so when ever I put any figure in
cell A1, I get figures automatically appearing in other cells as shown
above if I put 2.0 in cell A1 then I get 8.7 in B1 and 5.4% in C1. I
want to put formula in cell A1 as well and want to chane formulas in
cells B1 and C1 little bit so like this i'll have formulas in all
three cells. I want this because lets say if user put 8.7 in cell B1
then i want 2.0 to automatically appear in cell A1 and 5.4% to in C1.
In other words if user put figure any one of three cells then i want
remaing cells to produce result automatically. I tried putting
formulas in all three cells put i get circular reference error. I
know i can get rid of by ticking box of iterative calculation in
options but then i'll get different results. Is there any way that i
can have formulas in all three cells and if any one of the cells value
get change manually then remaing two show the correct results. and i
want this without having circular reference error. Please can any
friend can help

 

[K]

Hi rick, thanks for replying. i change you macro little bit but its
not working. i dont need whole column as i just need to specify the
ranges. any suggestion why its not working?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D18:F23,D26:F31,D35:F37,D40:F44"))
Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
Select Case Target.Column
Case 1
Target.Offset(, 1) = Target * 52 / 12
Target.Offset(, 2) = Target / 37
Case 2
Target.Offset(, -1) = Target * 12 / 52
Target.Offset(, 1) = Target * 12 / (37 * 52)
Case 3
Target.Offset(, -2) = 37 * Target
Target.Offset(, -1) = 37 * 52 * Target / 12
End Select
End If
Whoops:
Application.EnableEvents = True
End Sub

 

[Rick Rothstein]

Here is a minor change in my code which allows you to delete an entry and
not get zeroes in the other two cells (everything else works the same as in
my previously posted code)...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:C")) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
If Target = "" Then
Range("A" & Target.Row & ":C" & Target.Row).ClearContents
Else
Select Case Target.Column
Case 1
Target.Offset(, 1) = Target * 52 / 12
Target.Offset(, 2) = Target / 37
Case 2
Target.Offset(, -1) = Target * 12 / 52
Target.Offset(, 1) = Target * 12 / (37 * 52)
Case 3
Target.Offset(, -2) = 37 * Target
Target.Offset(, -1) = 37 * 52 * Target / 12
End Select
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

 

[Sam Wilson]

Case 4, 5 or 6 not 1,2,3 if it's columns D,E,F not A,B,C.

 

[Rick Rothstein]

As Sam said, you need to change the Case statements from Case 1, Case 2 and
Case 3 to Case 4, Case 5 and Case 6. The reason is because the Select Case
statement is looking at Target.Column where Target is the cell you changed
and Column is the number that the Target cell is in. The Case statements
represent the possible values that Target.Column can produce (which is 4, 5
and 6 as per your latest post).

By the way, for future reference... don't simply your problem for us when
you ask your question... doing that make additional work for you when you
have to modify any code we offer you and it, as you can now see, gives you
the opportunity to incorrectly try and change that code. Just tell us your
actual set up and what you want to be done with it and let us work with that
directly. And remember, examples are always helpful.

 

[K]

thanks sam and rick. you guys are brilliant