Circular Reference or not? And how?
Posted by Deirdre Marie on January 09, 2002 8:22 AM
Greetings~
In my worksheet I have a column (P) which totals the amount of Products produced that day.
Columns R, U, V, X, Y show the totals of the kinds of products produced that day and of course equal the total number in P. These numbers are manually entered - not calculated.
Due to a recent change in the way we account for products, I would like to have it "automated" for a couple of columns.
From now on, the number I enter in column V needs to be added to the number I entered in Column U. Further, the number I enter in column V also needs to be added to the total products column of P.
To illustrate ( helps me)
P is a calculated total of various columns A-O
As data is presented:
P Q R S T U V W X Y
20 - 4 - - 4 4 - 4 4
What I now need the worksheet to do:
P Q R S T U V W X Y
24 - 4 - - 8 4 - 4 4
I know how to add the V column figure to P to get an updated Product Total. However, my difficulty lies in getting column U to update when a number is entered in V.
If U was the result of a formula, I could do it very easily, however it is simply a number I enter to show the distribution of products.
I hope I was able to clearly explain my dilema.
Your assistance is greatly appreciated.
Posted by Tom Dickinson on January 09, 2002 10:21 AM
Not circular; formula elimination
I see 3 methods:
1) In U1 put the formula = 0 + V1. Of course this means that you have to enter your number using the formula bar (replacing the zero with the number you want), rather than entering directly into the cell. This form is not recommended.
2)Add another column before U that adds the value of U and V. If Column P uses Sum(Q1:Y1) you will need to add to the formula "-U1" so that product does not get double counted. To keep the look of the table the same, you could make the old column U (now column V) extremely narrow. That way it will almost disappear, yet you can still use your arrow keys to get to it. For safety purposes, I would lock the new column U to prevent deleting the formula by erroneously entering a number. This would be the preferred method.
3)Run a macro.
Private Sub DoubleV()
Dim Cnt as Integer
For Cnt = 1 to 500
Range("U" & Cnt) = Range("U" & Cnt) + Range("V" & Cnt)
Next
End Sub
You can change the numbers for the loop (1 and 500) to fit your situation. The problem with this method is that you can't run it twice on the same data. Eg if U = 3 and V = 2, then when you 1st run it, U will become 5. If you run it again without new data, U will become 7.
I'd take choice 2.
