@masrihan
I have come up with a VBA based solution which I think is most suitable in this scenario and easy to maintain.
In the attached, if you input an ID in C4 on Report Sheet, the data from all the month sheets will be fetched as desired.
The attached contains the following two codes.
Code on Module1:
Sub SummaryBasedOnID(ByVal ID As Long, NameCell As Range, PositionCell As Range, TotalIncomeCell As Range, MonthCell As Range)
Dim ws As Worksheet
Dim dictMonth As Object
Dim idCell As Range
Dim strName As String
Dim strPosition As String
Dim idFound As Boolean
Application.ScreenUpdating = False
MonthCell.CurrentRegion.Clear
NameCell.ClearContents
PositionCell.ClearContents
TotalIncomeCell.ClearContents
Set dictMonth = CreateObject("Scripting.Dictionary")
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "REPORT" Then
Set idCell = ws.Columns(2).Find(what:=ID, lookat:=xlWhole)
If Not idCell Is Nothing Then
idFound = True
If strName = "" Then strName = idCell.Offset(0, 1)
If strPosition = "" Then strPosition = idCell.Offset(0, 2)
dictMonth.Item(ws.Name) = idCell.Offset(0, 3).Value
End If
End If
Next ws
If idFound Then
NameCell.Value = strName
PositionCell.Value = strPosition
TotalIncomeCell.Value = Application.Sum(dictMonth.items)
TotalIncomeCell.NumberFormat = "#,##0.00"
MonthCell.Resize(dictMonth.Count, 1).Value = Application.Transpose(dictMonth.keys)
MonthCell.Offset(0, 1).Resize(dictMonth.Count, 1).Value = Application.Transpose(dictMonth.items)
With MonthCell.CurrentRegion
.Columns(1).Interior.Color = RGB(169, 208, 142)
.Columns(2).NumberFormat = "#,##0.00"
.Borders.Color = vbBlack
End With
Else
MsgBox "The ID " & ID & " was not found in any of the Months Sheets.", vbExclamation, "ID Not Found!"
End If
Application.ScreenUpdating = True
End Sub
Code on Report Sheet Module: Change Event Code which gets executed automatically once you change the content of the cell C4 on Report Sheet.
If you pay attention to the comments added in the code, you can easily change the cells to hold different information as per your requirement.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NameCell As Range
Dim PositionCell As Range
Dim TotalIncomeCell As Range
Dim StartMonthCell As Range
Set NameCell = Range("E4") 'Cell to hold the Name
Set PositionCell = Range("G4") 'Cell to hold the Position
Set TotalIncomeCell = Range("E7") 'Cell to hold the Total Income
Set StartMonthCell = Range("B6") 'Cell to display the months and corresponding income of the ID entered in C4
On Error GoTo Skip
If Target.Address(0, 0) = "C4" Then
Application.EnableEvents = False
If Target <> "" Then
SummaryBasedOnID Target.Value, NameCell, PositionCell, TotalIncomeCell, StartMonthCell
Else
NameCell = ""
PositionCell = ""
TotalIncomeCell = ""
StartMonthCell.CurrentRegion.Clear
End If
End If
Skip:
Application.EnableEvents = True
End Sub
If you add more month sheets with the data in same layout, the code will fetch the info from all of them and report it on Report Sheet.
