Keep relationship between 2 cells that both accept data

[Guest]

I swear I saw someone who posted a trick to accomplish this...

I want the value in cell A1 to always be twice the value in A2, but I want
to be able to enter data into either cell (not a formula).

For example, I type a 2 in cell A1 and A2 returns a 1. I then type 3 into
cell A2 and A1 returns a 6.

Is this possible?

 

[Guest]

Very easy with a little event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
Application.EnableEvents = False
If Intersect(Target, Range("A1")) Is Nothing Then
Range("A1").Value = 2 * Range("A2").Value
Else
Range("A2").Value = 0.5 * Range("A1").Value
End If
Application.EnableEvents = True
End If
End Sub

REMEMBER: This macro goes in the worksheet code area, not a standard module.

 

[Guest]

Yes, that certainly works. Now I'm trying to apply it to a more complicated
case...

My worksheet is made up of quarterly and annual data as follows:
Headings: Q1 Q2 Q3 Q4 2006 Q1 Q2 Q3 Q4 2007
Volumes: a b c d a+b+c+d e f g h
e+f+g+h
Mkt Shares: i% j% k% l% avg(i,j,k,l) (etc)

So I want the sheet to work such that if entering a quarterly volume, the
year just sums up the quarters. If entering an annual volume, it just places
in each of the quarters that value divided by 4.

What I would like it to do is this. If you enter a quarterly formula, it
places a formula in the cell for the year. If you enter a yearly volume, it
puts the same number in each of the four quarter cells. For the lines with
percentages it adjusts the formula accordingly.

The issue I have with the code you wrote is that I would have to
specifically identify all the ranges in the code. Is there a way to apply
code to a cell (similarly to how you apply a format) that tells it to do
something to the 4 cells to the left? In other words, I write code that says
when THIS cell is altered, put this number / 4 in each of the 4 cells to the
left. Then I apply this code to each of the columns that have yearly volumes.

Thanks for all your help!