Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Formula in all cells give Circular Reference. Please help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Formula in all cells give Circular Reference. Please help
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 "K" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Formula in all cells give Circular Reference. Please help
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) "K" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Formula in all cells give Circular Reference. Please help
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Formula in all cells give Circular Reference. Please help
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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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) "K" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Formula in all cells give Circular Reference. Please h
Case 4, 5 or 6 not 1,2,3 if it's columns D,E,F not A,B,C. "K" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Formula in all cells give Circular Reference. Please help
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. -- Rick (MVP - Excel) "K" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Formula in all cells give Circular Reference. Please help
thanks sam and rick. you guys are brilliant
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help in a circular reference | Excel Worksheet Functions | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Issues with my excel formula as it needs Circular reference - hlp | Excel Worksheet Functions | |||
circular reference formula | Excel Discussion (Misc queries) | |||
Avoiding circular reference on formula | Excel Discussion (Misc queries) |