Subtotalling Filtered Data Across Sheets

[RJB]

I have three sheets of data. (150,000+ rows of data).

I am using Filters to extract data I'd like to see.

I am using SUBTOTAL (3,|RANGE|) to count how many rows I have that fit that
filtered criteria, on each sheet.

I have a cell where I add the values of the SUBTOTALS for each sheet.

But I have to make sure that I have the *same* filters applied on all three
sheets. Is there a way to do that? Or to at least show what filter is applied
on other sheets?

Here's an example:
Let's say I have two columns: "Bands" (Col. A) and "Albums" (Col. B).
On Sheet1, I have all albums from the 1970's.
On Sheet2, I have all albums from the 1980's.
On Sheet3, I have all albums from the 1990's.
On Sheet4, I have all albums from the 2000's.

I want to know how many Bruce Springsteen albums I have total.

So, on each sheet, I select Data->Filter->AutoFilter.

On the Filter, I click on "Bruce Springsteen".

On each sheet, I have a cell (B1) with the following:
=SUBTOTAL(3,B3:B65536)

This returns the number of Bruce Springsteen records I have on each sheet.
(Sheet1=4, Sheet2=5, Sheet3=3, Sheet4=4).

That's all well and good, but I'd like to know how many TOTAL Springsteen
records I've got.

So on Sheet4, I have a cell C1:
=Sheet1!B1+Sheet2!B1+Sheet3!B1+B1

However, it's possible to screw up and accidentally select "Poison" on the
1980's tab (Sheet2). And I won't know it because I'm just looking at Sheet4.

SO.... Is there a way that I can select all my data that meets the filter
criteria ("Bruce Springsteen") on all sheets? If not, can I put "check" cells
in that will SHOW me what's selected on other sheets?

Thanks

 

[carlo]

Hmmm...just a suggestion:
As you have a looooot of rows of data, why don't you make a
database? In my opinion, that's exactly what databases are
for. Maybe you can even find a finished template on the web
for access or so.

If you insist on excel it get's rather complicated:

I would make a new sheet "overview" where you can select
your artists through a dropdown. (Population of dropout with
VBA, storing of the information in A1)
Then you have 4 cells:
A6 to A9 which would be like:
=countif(sheet1!A:A,A1)
and so on

then in cell A10 you have =sum(A6:A9)

hth

Carlo

 

[RJB]

Will be doing loads of statistical analysis on the results; Ihave Access,
client does not. So I don't know if Access will help much here.

I would make a new sheet "overview" where you can select
your artists through a dropdown. (Population of dropout with
VBA, storing of the information in A1)

And HOW do I do that?

 

[carlo]

How do you do what?
Populate the combobox? (no idea why i called it dropout, sorry
for that!)

for example you could do following

in your workbook module you can enter this code:
--------------------------------------------------------
Private Sub Workbook_Open()

Dim sh_ As Worksheet
Dim artists As New Collection
Dim val_

For Each sh_ In Worksheets
If Not sh_.Name = "overview" Then
For i = 1 To sh_.Cells(65536, 1).End(xlUp).Row
Set artists = Add_Artist(artists, sh_.Cells(i, 1).Value)
Next i
End If
Next sh_

For Each val_ In artists
If Not val_ = "" Then
Worksheets("overview").Cmb_Artist.AddItem val_
End If
Next val_

End Sub
--------------------------------------------------------
Function Add_Artist(Coll_ As Collection, NewName As String) As
Collection

On Error Resume Next

Coll_.Add NewName, NewName

Set Add_Artist = Coll_

End Function
--------------------------------------------------------

In the sheet overview, open the toolbox, and add a
combobox to the sheet, call it cmb_artist (or whatever,
but my code works with cmb_artist).

The code will then go through every sheet and add
each cell to the collection, skipping duplicates. in
the end the whole collection is assigned to the combo-
box.

Although I have no idea how limited the collection object is.
If you enter more than 150000 rows, maybe you will have
some memory problems, but you have to check that, never
worked with the collection object and that many rows.

On the other hand, you could of course make another
combobox, which offers the first letter of the artist, so you
can limit the output in your artist combobox.

hth, otherwise ask

Carlo