+ Reply to Thread
Results 1 to 12 of 12

How to automatically refresh filter by data shange

  1. #1
    Registered User
    Join Date
    01-10-2012
    Location
    Vilnius, Lithuania
    MS-Off Ver
    Excel 2011
    Posts
    7

    Question How to automatically refresh filter by data shange

    Hello everyone,

    first sorry for my english skills (i'm not speak english wery good), i will try to explain everything as clearly as possible about what i need. I hawe a lot of sheets in my workbook. In the sheet1 there is a list of students, lessons and their grades. In other sheets (2,3,4....) is the same lists by filtered criterias. For example, in sheet2 is 12th class students with grades, in sheet3 is 11th class students with grades, in sheet4 is 10th class students with grades and etc. In other sheets data changes automatically when i post something in my sheet1 (i'm using links for it). So, there are the same sheets like sheet1 just filtered by classes. In the sheet (2,3,4....) i am using filters. I want that all filters in shets (2,3,4,....) will automatically refresh when i changing or adding data on my sheet1 list. I am not a professionall in excel, i just understand some basics. It would be great if someone could help me

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: How to automatically refresh filter by data shange

    It would be good if you attach a sample workbook.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    01-10-2012
    Location
    Vilnius, Lithuania
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: How to automatically refresh filter by data shange

    Here you are
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-10-2012
    Location
    Vilnius, Lithuania
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: How to automatically refresh filter by data shange

    So, no one can help me..?

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: How to automatically refresh filter by data shange

    Will try something out for you by tomorrow.

  6. #6
    Registered User
    Join Date
    01-10-2012
    Location
    Vilnius, Lithuania
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: How to automatically refresh filter by data shange

    Thank you very much. Also I'm thinking, maybe it is possible to do a filter by using vlookup functio, but i dont know how to add more than 1 criteria and return not a 1 value, but entire row.

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: How to automatically refresh filter by data shange

    Is it possible for you to translate the details to english in your attachment?

  8. #8
    Registered User
    Join Date
    01-10-2012
    Location
    Vilnius, Lithuania
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: How to automatically refresh filter by data shange

    I tried to do this. Here you go.
    Attached Files Attached Files

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: How to automatically refresh filter by data shange

    This is more of a formula solution. Am sending it over to the experts.

  10. #10
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to automatically refresh filter by data shange

    here you go 2 helper columns on first sheet you could avoid this by using arrays but this would get a bit heavy with so many of them, also i just put class no. in c2 of each sheet
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  11. #11
    Registered User
    Join Date
    01-10-2012
    Location
    Vilnius, Lithuania
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: How to automatically refresh filter by data shange

    Thank You so much for this help (handshake) But, that's not everything i need These separate tables by classes was a fist thing I needed. I would like to ask you, what i need to do, that if student have no grade of some lessons, then in the filtered lists the function that you made will left an empty cell, not a "0"? Now if student have no grade of some lessons there are "0".

    Second and the last thing i needed is filtering separate classes students by their grades (assing students by grade groups). In other words, i need to find out what is group of student grades and then count how many students there are. In my workbook i added a sheet named "group and count". I need to count it in table1.

    In table2 i tried to explain the groups of these limits. There you see A,B,C,D,E rows named by red color. There are a grades criteria. If student have grades only from A rows, then he will be assigned to Group (9-10), but if he has one or more grades from Rows B, he will be assigned to (7-8) and so on.

    More examples:
    1. Almost all student grades is from Rows A, but one grade is from Rows B, then he will be assigned to Group (8-7).
    2. Almost all student grades is from Rows A and Rows B, but one grade is from Rows C, then he will be assigned to Group (6-4).
    3. Almost all student grades is from Rows A and Rows B, but one grade is from Rows C, then he will be assigned to Group (6-4).
    4. Almost all student grades is from Rows A, but one grade is from Rows E, then he will be assigned to Group (neatestuota).
    5. Almost all student grades is from Rows B, Rows C and Rows D so he will be assigned to Group (3-1).

    The easiest way to do this, is to add a column "Group of grades" in sheet named "Student grades" and make there a formula which finds and writes the group of students grades ((10-9) or (8-7) or (6-4) or (3-1) or (neatestuota)). After that i could use a =COUNTA() function to count students by their groups in table1

    I really hope some of excel masters understand what i explained and could help me I really appreciate your help.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-10-2012
    Location
    Vilnius, Lithuania
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: How to automatically refresh filter by data shange

    No matter, i solve this problem. Anyway thank's for helping.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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