Need to fill in data base on specific grades on other sheets !!

[bkcffee]

Hello forum
like title said,, I need to fill in data on the 4 yellow colored sheets in the attachment base on data from "Term 1 Sheet" or "Total Form" (Pick w/e easier)
1st sheet need to fill all Students who have Total Absent from 25 points and above
2nd sheet need to fill all Students who have Total Absent from 50 points and above
3rd sheet need to fill all Students who have Total Absent from 75 Points and above
4th sheet need to fill all Students who have 100 Remain Grades

Hope u understand wt I need,,,

Thanks in advance !!

[bkcffee]

if it needs VB or macros can any Mods transfer this topic to the right section plz :D

[AliGW]

Can you manually add the results you would expect for the sample data you've given?

Why do you need this data on lots of different sheets?

[missingN0]

Hi,


Without the use of macros I have added the following code to the <25 tab

=IFERROR(INDEX('Total Form'!A:A,SMALL(IF('Total Form'!$L:$L<25,IF('Total Form'!$L:$L>0,ROW('Total Form'!A:A),"")),ROW()-1)),"")

in Cell A2 and can be dragged across.


This is an array so Ctrl+Alt+Enter, I would also recommend limiting thecolumns to the length of the table i.e L2:L50 rather than L:L

[bkcffee]

Can you manually add the results you would expect for the sample data you've given?

Why do you need this data on lots of different sheets?

I know with filter copy n paste,,
tell that to my manager... :z

i'll try to add results in diff sheets, when I get back to the office

[missingN0]

The best solution would probably be using three pivot tables and filtering the data based Total Absent.


Kind regards

[JeteMc]

Here is a non array option.
On the three sheets that are populated based on number of absences populate column A using:

=IFERROR(INDEX('Total Form'!A$3:A$162,AGGREGATE(15,6,(ROW(A$3:A$162)-ROW(A$2))/('Total Form'!L$3:L$162<=25)/('Total Form'!B$3:B$162<>0),ROWS(A$1:A1))),"")

Note that on the Total Abs =<50 and Total Abs =<75 sheets the first part of the denominator will need to be modified as in: ('Total Form'!L$3:L$162<=50)
On the Remain Grade 100 Only sheet populate column A using:

=IFERROR(INDEX('Total Form'!A$3:A$162,AGGREGATE(15,6,(ROW(A$3:A$162)-ROW(A$2))/('Total Form'!M$3:M$162=100)/('Total Form'!B$3:B$162<>0),ROWS(A$1:A1))),"")

On all sheets the remaining columns may be populated using:

=IF(A2="","",INDEX('Total Form'!$B$3:$M$162,MATCH($A2,'Total Form'!$A$3:$A$162,0),MATCH(B$1,'Total Form'!$B$1:$M$1,0)))

Let us know if you have any questions.