+ Reply to Thread
Results 1 to 2 of 2

Autofilter on more than one sheet

  1. #1
    Registered User
    Join Date
    05-08-2008
    Posts
    1

    Autofilter on more than one sheet

    I got two questions regarding Filter -> Autofilter, but first a little about my workbook:

    I got a huge Excel workbook with several sheets (7). I got over 250 000 rows of data that I
    have divided in to 6 sheets (called Data 1, Data 2...), then I got another sheet named "Resultat"
    (Norwegian for result) where I fill inn all the criteria for calculations.

    The workbook consists on some values (measured on 30 positions at each object)

    I then use a SUMPRODUCT function in an IF function, based on the criteria I set in the sheet "Resultat"
    the two criteria I use is >,>=,=,=<,< an a value 0-7. My function then count the cells that match my
    desired criteria and list them up, based on their position (1-30) in each sheet, then I add all the "counted cells"
    together (listed up on positions) in "Resultat.

    My IF/SUMPRODUCT formula is:
    =IF(Resultat!$A$3="<";SUMPRODUCT(($C$3:$C$63682<Resultat!$B$3)*($E$3:$E$63682=G6));
    IF(Resultat!$A$3="<=";SUMPRODUCT(($C$3:$C$63682<=Resultat!$B$3)*($E$3:$E$63682=G6));
    IF(Resultat!$A$3="=";SUMPRODUCT(($C$3:$C$63682=Resultat!$B$3)*($E$3:$E$63682=G6));
    IF(Resultat!$A$3=">=";SUMPRODUCT(($C$3:$C$63682>=Resultat!$B$3)*($E$3:$E$63682=G6));
    SUMPRODUCT(($C$3:$C$63682>Resultat!$B$3)*($E$3:$E$63682=G6)) ))))

    Cell "A3" is where I fill inn ">,>=,=,=<,<"
    Cell "B3" is where I sett the value form 0-7

    My sheet set up:

    _______A___________B__________C___________D_____________E___
    1 Date measured |Object |Measurement 1 |Measurement 2 |Position
    2......................|............|.....................|.......................|.........
    3......................|............|.....................|.......................|.........

    Question 1:
    When I am using the "Autofilter" option to only list objects that starts with "A" (an hide all who does not),
    the values get hidden, but my calculations does not exclude the values that does not meet the criteria.
    Is it an easy way to fix that (I have read in the forum about the SUBTOTAL function, but I do not think that
    would help me)

    Question 2:
    Is it possible to in any way "centralize" the Auto filter options in sheet "Resultat" (so I just need to select the filtering
    there and not in each data sheet?

    Hope I did not scare you off with all the writing:D

  2. #2
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Good afternoon crazyleg

    ...and welcome to the forum!!

    Quote Originally Posted by crazyleg
    Question 1:
    but my calculations does not exclude the values that does not meet the criteria.
    No, Subtotal won't help you in this instance. Your best bet would be to use a helper column (which can be hidden if you like) containing a custom function that will change it's value if the row is hidden - which is essentially what happens when a column is filtered. The function below will return 1 if the row is visible, 0 if it is hidden. Then work an extra condition into your SumProduct formula, to only total rows that contain a 1.

    Please Login or Register  to view this content.
    Once you've put the code in, use the function :
    =HiddenRow(A2)
    assuming, A2 is the first row of the data you want to filter, and copy down.
    Of course, you won't be able to see tha value change, but trust me, it does...

    Not sure what you want for question 2 - can you clarify?

    HTH

    DominicB
    Last edited by dominicb; 05-08-2008 at 08:26 AM.
    Please familiarise yourself with the rules before posting. You can find them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1