Daniel Cremin
Board Regular
- Joined
- Feb 23, 2002
- Messages
- 64
Hi please could you help me sort through a problem im having with devising a solution to a complex issue!
Lets start at the beginning! It has taken me nearly an hour to write this whole thing in a way that would make sense to you the reader, so if you do decide to read the whole thing, i have tried my best to break it into small easy to understand pieces!!!
~I have an underlying worksheet of student records stored in 30 rows(row 3 being the 1st).
~ I am going to have a Struggling Students report worksheet which lists students who get a D, E, N and/ or U grade on 3 or more of the tests -
~the "Struggling"count of each student's grades, stored in each Student is handled by a Countif Statement, so no probs there.
Heres where it gets a little complex:
In order to be able to have total tally numbers of "Struggling" students from each of the 3 different class groups appear on the brilliant students report, i am going to have a special column of concatenated cell entries on the underlying worksheet which are a combination of the name of the class teacher and the number of "Struggling" performances (e.g. MrDickens4 - because the student's teacher is Mr Dickens and he struggled 4 times)
~ Originally i was going to just have a series of unique IF Statements will be used to evaluate each of the Struggling performances count cells (the one that counts the number of A*s and As)of the 30 student rows on the Underlying worksheet and decide if the number of the row that each IF statement evaluates should be entered or not based on the contents of the cell being greater than or equal to 3.
The first IF statement would have searched the Struggling count cell of the first student row on the underlying worksheet and if the student had struggled 3 or more times would enter the number "1" because this is the number of row that the cell it evaluates is out of the 30 student rows, and if the Student hadnt over-performed 3 times then a "0" would have been entered;
-The second IF statement cell searching the Struggling count cell of the 2nd student record row on the underlying worksheet would have entered "2" if the Struggling performance count had been greater than 3, and if not then a "0" would have been entered.
This trend continues for the other rows all the way down to 30.
OFFSET functions, set to start offsetting from the "cell header" of each column to retrieve information from would have Offset down by the number of rows specifed in the IF statement cell and retrieved this info - if 0 is entered then the "cell header's" text would be entered instead.
A For... Next statement would have been used to evaluate the 30 student IF statements and hide all rows which met the "searchvalue" of "0", so that only the Struggling Students appear.
However there is a very good reasons this no longer can be the case listed below:
I realised that if Less than 30 students are entered, that the 'template' row (that each student row is actually copied from and inserted above - its like a master key!) would also be visible on screen to the end-user due to the Functionality of its Struggling Performance Count cell detecting that 5 U grades are entered in this row (due to no values being entered in the Mark Cells that the Grades are based on via LOOKUP functions).
Here is what i have now come up with:
30 unique rows containg NESTED IF statement cells on the "Struggling Report" worksheet, which are each set to evaluate the CONCATANATED Chemistry Teacher and Struggling Performance Count dell (the combination of these two information items) for a range of possibilities and record the row of the Student being evaluated out of the 30 student rows, so that OFFSET functions using the Value entered in the NESTED IF powered cell as the number of rows to offset down by.
In the the First data row of the "Struggling Students Report" worksheet the Nested IF statemnt would enter the number "1" (because its evaluating the first student on the Underlying Worksheet)if the cell its being set to search (the CONCATANATED Teacher and Struggling Count informatio item)is equal to any of the following:
Mr Dickens3
Mr Dickens4
Mr Dickens5
Mr Temple3
Mr Temple4
Mr Temple5
Mrs Roberts3
Mrs Roberts4
Mrs Roberts5
else = 0
the second set of Nested IF statemnts, evaluating the second student row on the Underlying worksheet would search for the possible combinations already listed above and if they are True enter a "2", but if not any of them then a "0".
This trend continues for the other rows all the way down to 30.
A For...Next Macro could then be used to Evaluate the 30 Nested IF statement cells searching the range for the searchvalue=0 and using an IF... Then statement to hide all rows with the "0" entered in them so that only Struggling Students are displayed.
Basically what i want is any feedback regarding possible probs with implementing something like this and any helpful suggestions!
THANKS SO MUCH FOR READING ALL THIS!!!
Lets start at the beginning! It has taken me nearly an hour to write this whole thing in a way that would make sense to you the reader, so if you do decide to read the whole thing, i have tried my best to break it into small easy to understand pieces!!!
~I have an underlying worksheet of student records stored in 30 rows(row 3 being the 1st).
~ I am going to have a Struggling Students report worksheet which lists students who get a D, E, N and/ or U grade on 3 or more of the tests -
~the "Struggling"count of each student's grades, stored in each Student is handled by a Countif Statement, so no probs there.
Heres where it gets a little complex:
In order to be able to have total tally numbers of "Struggling" students from each of the 3 different class groups appear on the brilliant students report, i am going to have a special column of concatenated cell entries on the underlying worksheet which are a combination of the name of the class teacher and the number of "Struggling" performances (e.g. MrDickens4 - because the student's teacher is Mr Dickens and he struggled 4 times)
~ Originally i was going to just have a series of unique IF Statements will be used to evaluate each of the Struggling performances count cells (the one that counts the number of A*s and As)of the 30 student rows on the Underlying worksheet and decide if the number of the row that each IF statement evaluates should be entered or not based on the contents of the cell being greater than or equal to 3.
The first IF statement would have searched the Struggling count cell of the first student row on the underlying worksheet and if the student had struggled 3 or more times would enter the number "1" because this is the number of row that the cell it evaluates is out of the 30 student rows, and if the Student hadnt over-performed 3 times then a "0" would have been entered;
-The second IF statement cell searching the Struggling count cell of the 2nd student record row on the underlying worksheet would have entered "2" if the Struggling performance count had been greater than 3, and if not then a "0" would have been entered.
This trend continues for the other rows all the way down to 30.
OFFSET functions, set to start offsetting from the "cell header" of each column to retrieve information from would have Offset down by the number of rows specifed in the IF statement cell and retrieved this info - if 0 is entered then the "cell header's" text would be entered instead.
A For... Next statement would have been used to evaluate the 30 student IF statements and hide all rows which met the "searchvalue" of "0", so that only the Struggling Students appear.
However there is a very good reasons this no longer can be the case listed below:
I realised that if Less than 30 students are entered, that the 'template' row (that each student row is actually copied from and inserted above - its like a master key!) would also be visible on screen to the end-user due to the Functionality of its Struggling Performance Count cell detecting that 5 U grades are entered in this row (due to no values being entered in the Mark Cells that the Grades are based on via LOOKUP functions).
Here is what i have now come up with:
30 unique rows containg NESTED IF statement cells on the "Struggling Report" worksheet, which are each set to evaluate the CONCATANATED Chemistry Teacher and Struggling Performance Count dell (the combination of these two information items) for a range of possibilities and record the row of the Student being evaluated out of the 30 student rows, so that OFFSET functions using the Value entered in the NESTED IF powered cell as the number of rows to offset down by.
In the the First data row of the "Struggling Students Report" worksheet the Nested IF statemnt would enter the number "1" (because its evaluating the first student on the Underlying Worksheet)if the cell its being set to search (the CONCATANATED Teacher and Struggling Count informatio item)is equal to any of the following:
Mr Dickens3
Mr Dickens4
Mr Dickens5
Mr Temple3
Mr Temple4
Mr Temple5
Mrs Roberts3
Mrs Roberts4
Mrs Roberts5
else = 0
the second set of Nested IF statemnts, evaluating the second student row on the Underlying worksheet would search for the possible combinations already listed above and if they are True enter a "2", but if not any of them then a "0".
This trend continues for the other rows all the way down to 30.
A For...Next Macro could then be used to Evaluate the 30 Nested IF statement cells searching the range for the searchvalue=0 and using an IF... Then statement to hide all rows with the "0" entered in them so that only Struggling Students are displayed.
Basically what i want is any feedback regarding possible probs with implementing something like this and any helpful suggestions!
THANKS SO MUCH FOR READING ALL THIS!!!