Creating Summary Page from Multiple Worksheets by using a filter with Reference from Summary Page

[KotievdB]

I have one workbook with a summary sheet, an 5 to 7 input sheets (asset register) that I want to be able to select a dropdown on the summary sheet for say the branch and the workbook automatically filters all the input sheets by branch entries (all branches are included per asset category per input sheet) so that the summary sheet only reflects the Branch data.

In addition it must also go back to the global information, if say "ALL" is selected.

Have researched some VBA options, but is there no Excel button or add on that can do this? And what VBA options are out there?

 

[KotievdB]

Okay, used following VBA and it worked, slight glitch when I do all but calculates.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range


' The variable KeyCells contains the cells that will
' cause an alert when they are changed.

Set KeyCells = Worksheets("Summary").Range("M3")



If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then



Dim p As Integer, q As Integer




p = Worksheets.Count
For q = 3 To p

With Worksheets(q).Range("A6").AutoFilter(6, KeyCells)


End With
Next q


Dim p1 As Integer, q1 As Integer
p1 = Worksheets.Count
For q1 = 3 To p1


On Error Resume Next
If KeyCells = "ALL" Then Worksheets(q1).ShowAllData


Next q1


End If




End Sub