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!