Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Column A in a list of survey results is Products.
    Column B is users' assessments of 'quality' of that product, scale 1 to 5 or blank (= don't know or can't be asked).
    In cells elsewhere I want, for each of product1, product2 etc the Max, Min and Average score. [Ignore the blanks.]
    If someone can start me on any one of these, I think I can work out the others.
    Thanks in advance.
    JRR

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jrose' post='784241' date='12-Jul-2009 21:58']Column A in a list of survey results is Products.
    Column B is users' assessments of 'quality' of that product, scale 1 to 5 or blank (= don't know or can't be asked).
    In cells elsewhere I want, for each of product1, product2 etc the Max, Min and Average score. [Ignore the blanks.]
    If someone can start me on any one of these, I think I can work out the others.
    Thanks in advance.
    JRR[/quote]
    The easiest solution is a pivot table.
    A pivot table requires that the source table contains field names (column headers) in its first row. If you don't have those, insert them before proceeding.
    Click in any non-blank cell in the table.
    Select Data | Pivot Table and Pivot Chart Report...
    Excel should select the entire table.
    Click Next, then Next again.
    Click Layout...
    Drag the column A header to the Row area.
    Drag the column B header to the Data area.
    Double click the button in the data area.
    Select Max as summary function.
    Drag the column B header to the Data area (yes, again!)
    Double click the button in the data area.
    Select Min as summary function.
    Repeat another time, this time selecting Average as summary function.
    Click OK.
    You can choose whether you want the pivot table to be created on a new worksheet or on an existing worksheet (and specify where it should be).
    Click Finish.

    Note: pivot tables aren't updated automatically if the source data change. To update, click in the pivot table and click the Refresh Data button on the Pivot Table toolbar (the exclamation mark).

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    cool, Hans.
    Is there a way using an 'event' (eg any change to the results list) to provoke the pivot table to be refreshed?

    Note: pivot tables aren't updated automatically if the source data change. To update, click in the pivot table and click the Refresh Data button on the Pivot Table toolbar (the exclamation mark).
    [/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, using the Worksheet_Change event. Assuming that the pivot table is on the same worksheet as the source data:

    - Right-click the sheet tab.
    - Select View Code from the popup menu.
    - Enter or copy/paste the following code into the module:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Range("A:B"), Target) Is Nothing Then
    	Application.EnableEvents = False
    	Me.PivotTables(1).RefreshTable
    	Application.EnableEvents = True
      End If
    End Sub
    - Switch back to Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •