How do I take the averages of the values on different sheets and present in a gi

Question

How do I take the averages of the values on different sheets and present in a given cell.

My token workbook has values in a range C5:C9 on 5 sheets in as follows:

'1'!C5 = 1, '2'!C5 = 2, '3'!C5 = 3, '4'!C5 = 4, '5'!C5 = 5,

'1'!C6 = 2, '2'!C6 = 4, '3'!C6 = 6, '4'!C6 = 8, '5'!C6 = 10,

'1'!C7 = 3, '2'!C7 = 6, '3'!C7 = 9, '4'!C7 = 12, '5'!C7 = 15,

'1'!C8 = 4, '2'!C8 = 8, '3'!C8 = 12, '4'!C8 =16, '5'!C8 = 20,

'1'!C9 = 5, '2'!C9 = 10, '3'!C9 = 15, '4'!C9 = 20, '5'!C9 = 25,

When I click on any cell in the range C5:C9, I would like the average of all sheets of that cell to appear in cell H6.

For example, when I click on C6, I would like the average of cells C6 for all sheets to appear in cell H6.   

In this case:

Click on C7 and 9 appears in cell H6
Click on C9 and 15 appears in cell H6.

Eventually, I will have a much larger range for the application.

BONUS:

When I click on C7, the title in A7 for that row will show up in G6.

Click on C9 and the title in A9 for that row will show in G6.

Etc for all cells in the range.

I hope this is enough for someone to understand the task.

Thanks !!

Explanation / Answer

Hi...

Put the code below into the main worksheet's code module:

Right-click on that sheet's name tab and choose [View Code] and then paste the code into the module presented to you. Close the VBA Editor, and save the workbook as a macro enabled workbook, type .xlsm or .xlsx. Give it a trial.

The code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("C5:C9")) Is Nothing Then
    If Target.Cells.Count = 1 Then
      Range("H6") = __
       (ThisWorkbook.Worksheets("1").Range(Target.Address) _
        + ThisWorkbook.Worksheets("2").Range(Target.Address) _
        + ThisWorkbook.Worksheets("3").Range(Target.Address) _
        + ThisWorkbook.Worksheets("4").Range(Target.Address) _
        + ThisWorkbook.Worksheets("5").Range(Target.Address)) / 5
      Range("G6") = Range("A" & Target.Row)
    End If
End If
End Subb

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.