+ Reply to Thread
Results 1 to 5 of 5

retrieve a specific row of data after criteria selection?

  1. #1
    Registered User
    Join Date
    06-27-2010
    Location
    Bangkok, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    4

    retrieve a specific row of data after criteria selection?

    Hi guys! I am doing this Excel trend tracking project but got stuck at one point and really need your help!! Thanks in advance!

    So I collected students' trial tests scores from classes in one year. I input all the data into excel already. so in column A, i put ClassA-E; in column B, each class has rows labeled students No.1-10. Then starting from columnC are all the trial scores spread out horizontally.

    Trial 1 Trial 2 Trial 3
    Student 1 30 22 30
    Student 2 34 26 34
    C Student 3 38 30 38
    L Student 4 42 34 42
    A Student 5 46 38 46
    S Student 6 50 42 50
    S Student 7 54 46 54
    Student 8 58 50 58
    A Student 9 62 54 62
    Student 10 66 58 66


    So if I only want data(scores) of student10 from classA, which formulas/whatever works should i use to retrieve the data from my database? I am thinking about having buttons/dropsown list on the class and student number selections, to specify my criteria.

    Thank you so much for your help!! I'd really appreciate any idea!!

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    re: retrieve a specific row of data after criteria selection?

    Put filter (data -> filter) in first row and filter out what student and what classess you want to see.

  3. #3
    Registered User
    Join Date
    06-27-2010
    Location
    Bangkok, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    4

    re: retrieve a specific row of data after criteria selection?

    Quote Originally Posted by zbor View Post
    Put filter (data -> filter) in first row and filter out what student and what classess you want to see.
    Thanks for the idea! But what if I want my after-filter-selection to appear in another worksheet? i want the user to work on a cleaner looking place that is not the database sheet..Thanksss!

  4. #4
    Registered User
    Join Date
    06-28-2010
    Location
    hyderabad
    MS-Off Ver
    Excel 2003
    Posts
    16

    re: retrieve a specific row of data after criteria selection?

    Use
    DSUM or Sumproduct functions

    =Sumproduct(($A$1:$A$1000="ClassA")*($B$1:$B$1000="Student1")*(C$1:C$1000))

  5. #5
    Registered User
    Join Date
    06-27-2010
    Location
    Bangkok, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    4

    re: retrieve a specific row of data after criteria selection?

    Thanks so much guys! The formulas all worked out!!



    I have another problem tho (bear me plsssssss XD)"



    Student 1 Student 2 Student 3 Student 1 Student 2 Student 3 Student 1
    Trial 1 Trial 2 Trail 3 Trial 1 Trial 2 Trail 3 Trial 1 Trial 2 Trail 3
    Class A 30 69 55 88 101 117 133 149 165
    Class B 32 65 59 89 103 119 136 152 169
    Class C 34 61 63 90 105 122 139 156 173
    Class D 36 57 67 91 107 124 142 159 177
    Class E 38 53 71 92 109 127 145 163 181
    Class F 40 49 75 93 111 129 148 166 185
    Class G 42 45 79 94 113 132 151 170 189

    Class A
    Student 1 #VALUE!
    Trial 1




    So now i want to use 3 criteria, as shown above, to specify the data: class, student and trial number. I tried to modify the SUMPRODUCT formulas to

    =SUMPRODUCT(--(A3:A9<-class array=A11),--(B1:J1<-student array=A12),--(B2:J2<-trial array=A13),(B3:J9)<-the whole numbered area)



    but it didnt work out. So could you guys pls help me out again? maybe i should use match+index+lookup?? i am really lost. THANKS!

+ 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