On Jun 13, 2:14 pm, "Ron de Bruin" wrote:
You can use this event in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("B13:B30"), Target) Is Nothing Then
Range("D3").Formula = "=average(b" & Target.Row - 11 & ":b" & Target.Row & ")"
End If
End Sub

Change B13:B30 to the range that must activate the code

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



wrote in ooglegroups.com...
I have a simple worksheet that has two columns of data, column A is
month and year, column B is a four digit number. What I'm looking to
do is get an average when a cell is selected. The average would
populate in cell D3. So, let say I click or select cell B18, the
formula should be something like =average(b7:b18) for cell D3. In
other words when a cell is select that the average counts back 12
cells up. I'm looking to get the average number for a 12 month span.
If I select cell B19, then the code should be for an average
=average(b8:b19). Make sense?

Any ideas on some vba code that would do this?- Hide quoted text -

- Show quoted text -

Thanks for the comments, I used the one from Ron de and it worked very
well....thanks a ton!