Thread: Sort pages?
View Single Post
  #15   Report Post  
David McRitchie
 
Posts: n/a
Default

Hi David,

Perhaps the example at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
might be helpful. I emailed an example based on sketchy details
you posted.

Don't know if all sheets have the same format, if a date is one day
or a semester. Don't know if all students take the same classes
that was my assumption; otherwise, the class report is not
going to work.

I think you might be able to do this with a couple of Pivot Tables
if the data is strictly

studentname, class, date

see http://www.contextures.com/tiptech.html
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David" wrote in message ...
David McRitchie wrote

Hi David Turner,
How about the alternative, easy enough to sort the worksheets
http://www.mvps.org/dmcritchie/excel...#sortallsheets
and to have a master sheet and total on the master sheet
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

I don't know what you mean by a subtotal in the sheets,
as long as the total is for just that sheet you simply use
SUM or even SUBTOTAL

To me the original seems so unwieldy maintaining a page
worth of data (many rows) for each student and every student
within a single worksheet.
If it were one row of data per student then you would not
have a problem with sorting. But from what I can get from
your arrangement it seems it would be difficult to find
where to update a student.

HTM,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


David, thanks for jumping in.
The workbook was provided to me by another school. Users there have to
update it manually and I'm trying to simplify the process to properly
handle adding/removing students, yet maintain the integrity of the
workbook. I came across a snipit of code to handle regenerating page
breaks, but needed something to sort the results of adding a student
before running it.

As for the alternative, I did grab the sortallsheets routine in case I
get permission to alter the layout so each student has their own sheet.
That does seem like it would be easier to maintain. I've never used a
TOC, so I'll have to have a look.

Each student has several classes with a total for hours spent in each
class and total class time for the month, and then another page at the
bottom that totals those totals for a grand total of hours spent in each
class by ALL students during the month. The original file uses SUM for
each grand total #, and the formulas are looonnnggg and they now have to
be updated manually if a new student is added or a student is removed, so
I'm thinking SUBTOTALs in place of SUMs would be better.

To give you a brief picture of original layout:

Page 1
Dates across row, starting in Col2
StudentName1
ClassName1 TotalHrs1
ClassName2 TotalHrs2
ClassName3 TotalHrs3
SUM Monthly Student1 class time

Page 2
Dates across row, starting in Col2
StudentName2
ClassName1 TotalHrs1
ClassName2 TotalHrs2
ClassName3 TotalHrs3
SUM Monthly Student2 class time

Page X
Class1 SUM monthly hrs Class1 all students
Class2 SUM monthly hrs Class2 all students
Class3 SUM monthly hrs Class3 all students

Page for each student printed monthly and filed in their chart.
Page X for Utilization Review.

I'm tackling simplifications one at a time, so I may be seeking others in
the future <g.

--
David