I really, really dislike non-tabular datasets and systems that spew out data in a spreadsheet format!
The only way to crunch any meaningful summary out of such dataset is through some extensive formula usage and the subsequent analysis is an incredible pain to maintain on a regular basis.
On top of it, filtering can be another problematic aspect.
Using a table (the so called list object) can alleviate some pain, but it can still be annoying if you’ve got a spreadsheet full of them and you need to make sure that they can be re-filtered consistently without too much user effort.
One way to ensure consistency in filtering is to use a VBA code, which will loop through all tables in the workbook and re-filter them accordingly
|Dim Wks As Worksheet|
|Dim T As ListObject|
|Dim i As Integer|
|Dim bU As String|
|bU = ActiveSheet.Range("C6").Value|
|For Each Wks In ThisWorkbook.Worksheets|
|If Wks.Visible = True Then Wks.Activate|
|If ActiveSheet.ListObjects.Count > 0 Then|
|For Each T In ActiveSheet.ListObjects|
|If bU = "All" Then|
|T.Range.AutoFilter Field:=1, Criteria1:=bU|
Happy VBA coding!