• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summary Table from Other Worksheets

fixthis

New Member
I am in need to create a summary table of cumulative points for each participant that has entered in monthly competitions. Currently I have a summary worksheet that I have to manually enter names when new members join and submits to the competition. I am looking for a way to automatically populate the summary table Name column D base on whether the name shows up in any of the worksheets named Jan 2015 through Nov 2015 in range L12:L40 (this range varies in size). I have created a fictitious competition for Jan and Feb worksheets for purposes of testing.

I am working in Excel 2003.

Cross posted at http://www.mrexcel.com/forum/excel-questions/839331-creating-sub-table-based-presence-other-tables.html

Thanks
 

Attachments

  • 2015 Comp TestV2.xls
    726 KB · Views: 15
Pls check this...


Code:
Option Explicit

Sub test()
Dim ws As Worksheet, r As Range

Application.ScreenUpdating = False
With CreateObject("scripting.dictionary")
    For Each ws In ThisWorkbook.Worksheets
        If IsDate(ws.Name) Then
            For Each r In ws.Range("L12:L40")
                If Len(r) > 0 And Not .exists(r.Value) Then .Add r.Value, 1
            Next
        End If
    Next
    Sheets("2015 Summary by Category").Range("D6:D52") = ""
    Sheets("2015 Summary by Category").Range("D6").Resize(.Count).Value = Application.Transpose(.keys)
End With
Application.ScreenUpdating = True

End Sub
 
Thank you Deepak that worked spot on.

Would it be possible to extend that same idea to grab the totals in column H of each worksheet for the names by category? Either in a separate code (macro) or in the same code.
 
Thank you Deepak that worked spot on.

Would it be possible to extend that same idea to grab the totals in column H of each worksheet for the names by category? Either in a separate code (macro) or in the same code.

Little more specific with a sample is req.
 
Using the attached file in the first post.
At Worksheet "2015 Summary by Category", for each name in column D, I would like to sum the corresponding total points in column H from all worksheets having month and year (i.e., Jan 2015) for the corresponding name (produced by your Macro above) and Category.

For example: Adam (row 6) will have 4 points under Nature because there is a 4 at cell H47 on the Feb 2015 worksheet where his name and the code NA (for Nature Advanced) is used to create a unique combination. Currently I used the following formula to do this.

Code:
=SUMPRODUCT(SUMIF(INDIRECT("'"&ShtList&"'!X9:X120"),LEFT(G$5,1)&"*|"&$D6,INDIRECT("'"&ShtList&"'!H9:H120")))

So I am looking for a macro that can basically do what the formula does above hopefully without needing the helper column (hidden column X) on each worksheet.

ShtList is a list of the sheet names Jan 2015, Feb 2015 etc.
 
Back
Top