Generate consolidate list dynamically from multiple sheets

[jb]

Hello Helpers,

Trying to explain my problem. I have an excel file for maintaining list of students participating in various events of an institute.

I have one sample excel which contains 4 sheets right now.

1. First sheet "event_list" has list of sheet names under column title "Event Sheet Title". Right now there 2 names in cell b4 and b5 - tennis and cricket respectively. Tennis and cricket are 2 activities arranged for students. Note: There can be new sheets of new sports events can be added in future. Also, in one sheet, there can be multiple entry of one student for various time period.
2. Now in sheet tennis and cricket, I have list of students from various classes who have participated in tennis and cricket event.
3. I want to generate consolidated list of students in the last consolidated sheet. In this sheet, I want to provide drop down list from which user can select class name. Here, it should display list of all the students of respective class who have participated in all the events in sorted order of Enrollment number.

This data is required to calculate leaves of students. Sample calculation given in attached file. I have calculated consolidated sheet manually to explain the type of report required to be generated. This list must be dynamically generated from the list of events entered on first sheet. When event is added, consolidated report must be automatically updated.

Please provide me a solution with formula or vba code. Power query is not suitable for us.

 

[vletm]

jb​

What is different with one of Your previous thread?

Generate list from multiple sheets based on criteria

Hello Helpers, I have for example 4 sheets right now. First sheet "event_list" has list of sheet names under column title "Event Sheet Title". Right now there 2 names in cell b4 and b5 - tennis and cricket respectively. Note: There can be new sheets of new sports events can be added in future...

chandoo.org

 

[Monty]

Hello Helpers,

Trying to explain my problem. I have an excel file for maintaining list of students participating in various events of an institute.

I have one sample excel which contains 4 sheets right now.

1. First sheet "event_list" has list of sheet names under column title "Event Sheet Title". Right now there 2 names in cell b4 and b5 - tennis and cricket respectively. Tennis and cricket are 2 activities arranged for students. Note: There can be new sheets of new sports events can be added in future. Also, in one sheet, there can be multiple entry of one student for various time period.
2. Now in sheet tennis and cricket, I have list of students from various classes who have participated in tennis and cricket event.
3. I want to generate consolidated list of students in the last consolidated sheet. In this sheet, I want to provide drop down list from which user can select class name. Here, it should display list of all the students of respective class who have participated in all the events in sorted order of Enrollment number.

This data is required to calculate leaves of students. Sample calculation given in attached file. I have calculated consolidated sheet manually to explain the type of report required to be generated. This list must be dynamically generated from the list of events entered on first sheet. When event is added, consolidated report must be automatically updated.

Please provide me a solution with formula or vba code. Power query is not suitable for us.

Check this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Triggered when there is a change in the "Event List" sheet
    
    Dim eventListSheet As Worksheet
    Dim consolidatedSheet As Worksheet
    Dim eventCell As Range
    Dim eventName As String
    
    ' Set references to the relevant sheets
    Set eventListSheet = ThisWorkbook.Sheets("event_list")
    Set consolidatedSheet = ThisWorkbook.Sheets("Consolidated")
    
    ' Check if the changed cell is in the "Event Sheet Title" column
    If Not Intersect(Target, eventListSheet.Range("B:B")) Is Nothing Then
        Application.EnableEvents = False ' Prevent triggering events while making changes
        
        ' Clear previous data in the "Consolidated" sheet
        consolidatedSheet.Rows("2:" & consolidatedSheet.Rows.Count).ClearContents
        
        ' Loop through each event in the "Event List" sheet
        For Each eventCell In eventListSheet.Range("B4:B" & eventListSheet.Cells(eventListSheet.Rows.Count, "B").End(xlUp).Row)
            eventName = eventCell.Value
            
            ' Call function to copy data from each event sheet to the "Consolidated" sheet
            CopyDataToConsolidated eventListSheet, consolidatedSheet, eventName
        Next eventCell
        
        Application.EnableEvents = True ' Enable events again
    End If
End Sub

Private Sub CopyDataToConsolidated(ByVal eventListSheet As Worksheet, ByVal consolidatedSheet As Worksheet, ByVal eventName As String)
    ' Copy data from each event sheet to the "Consolidated" sheet
    
    Dim eventSheet As Worksheet
    Dim lastRow As Long
    
    ' Check if the event sheet exists
    On Error Resume Next
    Set eventSheet = ThisWorkbook.Sheets(eventName)
    On Error GoTo 0
    
    If Not eventSheet Is Nothing Then
        ' Find the last row in the event sheet
        lastRow = eventSheet.Cells(eventSheet.Rows.Count, "A").End(xlUp).Row
        
        ' Copy data to the "Consolidated" sheet based on the selected class
        eventSheet.Range("A2:D" & lastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
            eventListSheet.Range("F1:F2"), CopyToRange:=consolidatedSheet.Range("A2"), Unique:=False
        
        ' Sort the data in the "Consolidated" sheet based on Enrollment number
        consolidatedSheet.Sort.SortFields.Clear
        consolidatedSheet.Sort.SortFields.Add Key:=consolidatedSheet.Range("B2:B" & consolidatedSheet.Cells(consolidatedSheet.Rows.Count, "B").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With consolidatedSheet.Sort
            .SetRange consolidatedSheet.Range("A1:D" & consolidatedSheet.Cells(consolidatedSheet.Rows.Count, "D").End(xlUp).Row)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
End Sub

 

[jb]

jb​

What is different with one of Your previous thread?

Generate list from multiple sheets based on criteria

Hello Helpers, I have for example 4 sheets right now. First sheet "event_list" has list of sheet names under column title "Event Sheet Title". Right now there 2 names in cell b4 and b5 - tennis and cricket respectively. Note: There can be new sheets of new sports events can be added in future...

chandoo.org

Sir, In previous thread, I received answer with power query which is not available to and we are very basic users us so we couldn't understand even. One solution was given with formula but we failed in first step. I replied also that we couldn't proceed from first step.
I waited in that thread but not got solution. I thought, I might not have explained my problem properly.
So, I wrote new thread and tried to explain problem in detail.

 

[vletm]

jb​

Do not duplicate Your thread.
If You have same thread then please, write more in same thread - what do You really would like to get?
If You would like to help to something else ... then You'll open a new thread.
This duplicated thread is closed now.