Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotalling Filtered Data Across Sheets
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotalling Filtered Data Across Sheets
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 On Dec 5, 4:24 pm, RJB wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotalling Filtered Data Across Sheets
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotalling Filtered Data Across Sheets
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 On Dec 5, 10:22 pm, RJB wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotalling | Excel Worksheet Functions | |||
Subtotalling | Excel Worksheet Functions | |||
data subtotalling isnt working properly on my spreadsheet | Excel Worksheet Functions | |||
accumulate filtered records from several sheets to one | Excel Discussion (Misc queries) | |||
Pasting on Filtered Data Sheets without pasting onto hidden cells | Excel Discussion (Misc queries) |