Jake,
I think you could accomplish this pretty easily with just one work sheet
rather than multiple worksheets, especially since classes are usually
guaranteed to be within a limited size. I'll give you pointers on how to
do this using one worksheet (which assumes you have full control of all
data entry), then at the end I'll show you how to make this work the way
you originally requested if you decide that one sheet won't fit your
situation (like if each teacher enters the grades for their own classes,
in which case I would generally recommend setting up separate workbooks
with restricted permissions, not one workbook with separate worksheets).
ONE WORKSHEET WAY
On your "All_Classes" worksheet, set up your headers across the top. A is
Class #, B is student name, C is attendance/absences, D is 1st Semester
overall grades, E is 1st Semester assignment grades, F is 1st Semester
test grades, G is 1st Semester midterm exam grade, H is 1st Semester final
exam grade, whatever your heart desires. Even SAT/ACT scores. You can
drill down your headers to be as detailed or general as you want, so long
as you have the data to fill it.
Copy your student information and marks over from Sheet1, then from
Sheet2, etc. until all students are on this worksheet. As you copy, make
sure you fill in the number 1 for all Class 1 folks in column A, and so on
for all classes.
Obviously, we need room to add more students later. Using your max class
size * number of classes (such as 60 students max per class * 5 classes
would put you at row 301), make that row (301) permanently blank and begin
your stats/totals key in the next row. You can resize the height of this
blank row to 4 or 8 to make it less obnoxious.
For stats/totals, here are the steps to have all totals for each class and
overall totals always visible:
If Z is your last column, then in AA302 put 1, in AA303 put 2, and then
highlight those 2 and drag the fill handle down for as many classes as you
have (for 5 classes, we'll stop at row 306).
Next, it's time for totals formulas
A302 ="Class "&AA302&" Totals"
B302 =COUNTIF($A$2:$A300,AA302)
C302 =SUMIF($A$2:$A$300,$AA302,C$2:C$300)/$B302 ->then use the fill
handle on C302 to drag across to the last column (Z). Highlight A302 to
Z302 and use the fill handle to drag those formulas down to row 306.
A307 "Overall Totals"
B307, hit Alt+Enter to autosum A302:A306. Use the fill handle to drag
that across to column Z.
Finally, select all your headers and potential data area above the blank
row (A1:Z300), assign it the name "Entire_Dataset" and apply autofilter.
Now you can look at just class 1 data by filtering column A for 1, or you
can look at everyone by filtering column A for "nonblanks." When you need
to delete a student, simply highlight their data (let's say A99:Z99) and
hit the delete key. If you need to add a student, unfilter column A to
show "All" and start typing in the first available row (like B190) and
start putting in their data. As you have adds/deletes, you may need to
resort your list occassionally: Edit -> GoTo -> Entire_Dataset and then
Data -> Sort. You can sort or filter this thing a myriad of ways...who
all had 0 absences, who all scored 100 in one area or overall. You could
even set this thing up for Advanced Filter and reserve rows 1 through 12
for some criteria, such as grades >=90, >=80 and <90, etc.
If you only want totals to show for the class you are looking at and the
overall, you can do that as well. Just let me know if you would rather
have that.
MULTIPLE WORKSHEETS WAY
If you'd really rather keep the individual classes on their own worksheets
(say, each teacher is responsible for typing in their own data, so you
have a separate workbook for each Class), then you can just use simple
lookup formulas on "All_Classes". I assume your worksheets or workbooks
now do not have a column for class number, so on each of them put the
number 1 or 2 or 3 in cell AA2 and drag that number down to your max class
size on each sheet.
On "All_Classes", set up your headers to match the other worksheets. If
you use separate workbooks, the syntax is 'File Location\[Workbook
Name]Sheet1'!
A2 =IF(B2=0,"",Sheet1!AA2)
B2=Sheet1!A2 -> then you can use the fill handle on B2 to drag the formula
across as far as you need (like Z2).
Select A2:Z2 and use the fill handle to drag those formulas down the 60 or
so max rows you need.
Copy A61:B61 to A62:B62 and change Sheet1 to Sheet2 and the row to 2. Now
drag B62 across to Z62, then drag A62:Z62 down 60 rows, etc. Lather,
rinse, repeat.
When you're done, filter column A for "NonBlanks" and always leave that
filter engaged.
The only difference with this approach is that you must do your data entry
and sorting on each worksheet or workbook. You can still use
"All_Classes" for filtering and totals but not for sorting or data entry.
Hope this helps!
-KC
Jakem Schott said:
Thanks for the reply KC
Would this work for the following case example. Sheet1 contains a
variable
number of students in a class and the marks they received for an
assessment,
so all records may not be unique but are relevant in their own right,
class 2
is on sheet 2, class 3 is on sheet 3. What I would then like to do is
combine
all the classes results on one page and perform sort and statistical
analysis
on the full resulting list.
Thanks again
Jake
