Using XL2000:

I have a single page spreadsheet (needed for SubTotal() to work), separated
into 65 sheets via Page Break Preview. The top cell of each page in Col A
contains the student's name (last name, first name). If I add a student,
how can I then sort the pages by the student's last name?

Acceptable alternative: Have each student on their own page with sheets
named Lastname Firstname, sort those, and still be able to Subtotal in
cells on those sheets, then Grand Total on a separate sheet.

--
David

David,

Not sure if this is the best way, but you could use a column containing the
Lastname of the student for each line of the student's page. You could then
sort on this column. You could either hide the column for printing purposes
or use the PrintArea field to exclude it.

Hope that helps.

"David" wrote:

Using XL2000:

I have a single page spreadsheet (needed for SubTotal() to work), separated
into 65 sheets via Page Break Preview. The top cell of each page in Col A
contains the student's name (last name, first name). If I add a student,
how can I then sort the pages by the student's last name?

Acceptable alternative: Have each student on their own page with sheets
named Lastname Firstname, sort those, and still be able to Subtotal in
cells on those sheets, then Grand Total on a separate sheet.

--
David

Use a Helper column with the Name and a suffix of a sequential number, like
Brown,Charlie1 for row one of Charlie Browns data, then Brown,Charlie2 for
his second row, etc etc........then Armstrong,Jack1....Armstrong,Jack2 for
the next group etc etc...........they don't have to be sorted by name, then
sort on this column and they sort as a group by name, and stay in the proper
order as well..........

Vaya con Dios,
Chuck, CABGx3


"David" wrote in message
...
Using XL2000:

I have a single page spreadsheet (needed for SubTotal() to work),
separated
into 65 sheets via Page Break Preview. The top cell of each page in Col A
contains the student's name (last name, first name). If I add a student,
how can I then sort the pages by the student's last name?

Acceptable alternative: Have each student on their own page with sheets
named Lastname Firstname, sort those, and still be able to Subtotal in
cells on those sheets, then Grand Total on a separate sheet.

--
David

Did you use Data|Subtotal to add the =subtotals()?

If yes, then I'd remove those subtotals, add the new person, sort the data and
reapply data|subtotals.

If you do this lots of times, you may want to record a macro when you do it.


David wrote:

Using XL2000:

I have a single page spreadsheet (needed for SubTotal() to work), separated
into 65 sheets via Page Break Preview. The top cell of each page in Col A
contains the student's name (last name, first name). If I add a student,
how can I then sort the pages by the student's last name?

Acceptable alternative: Have each student on their own page with sheets
named Lastname Firstname, sort those, and still be able to Subtotal in
cells on those sheets, then Grand Total on a separate sheet.

--
David

--

Dave Peterson

Dave Peterson wrote

Did you use Data|Subtotal to add the =subtotals()?

If yes, then I'd remove those subtotals, add the new person, sort the
data and reapply data|subtotals.

If you do this lots of times, you may want to record a macro when you
do it.

No, I didn't. I just put =Subtotal() formulae in desired cells.
Thanks for the input.

--
David

?B?RG9taW5pYw==?= wrote

David,

Not sure if this is the best way, but you could use a column
containing the Lastname of the student for each line of the student's
page. You could then sort on this column. You could either hide the
column for printing purposes or use the PrintArea field to exclude it.

Hope that helps.

Sad to say, that moves all the names to the top and data under each name to
the bottom. Thanks for trying, though.

--
David

CLR wrote

Use a Helper column with the Name and a suffix of a sequential number,
like Brown,Charlie1 for row one of Charlie Browns data, then
Brown,Charlie2 for his second row, etc etc........then
Armstrong,Jack1....Armstrong,Jack2 for the next group etc
etc...........they don't have to be sorted by name, then sort on this
column and they sort as a group by name, and stay in the proper order
as well

That works, as I was wanting to leave the several rows of data under each
name "connected" to the student. Rather cumbersome to do initial setup
because of that data, though. It's made a little easier by the fact that I
can drag fill sequence for each student, but still....

--
David

CLR wrote

Use a Helper column with the Name and a suffix of a sequential number,
like Brown,Charlie1 for row one of Charlie Browns data, then
Brown,Charlie2 for his second row, etc etc........then
Armstrong,Jack1....Armstrong,Jack2 for the next group etc
etc...........they don't have to be sorted by name, then sort on this
column and they sort as a group by name, and stay in the proper order
as well..........

Followup on initial setup: If I used formulas in helper column for the
first student like =A2&1, =A2&2, =A2&3, etc., where A2 held the first
student's name, selected those and drag-filled down, it took most of the
pain out.

--
David

Followup on your followup <g
Although the concatenated suffix's must be in sequence for each group, they
need NOT start with 1 each time..........sooo, you could just use another
helper column with numbers sequenced from 1-XXXX, and then concatenate the
names and the respective row from this new column, so it might read,
Brown,Charlie1....Brown,Charlie2......Armstrong,Ja ck12......Armstrong,Jack13
, etc etc..........would make the initial setup easier, just type in one
formula and copy down................



Vaya con Dios,
Chuck, CABGx3


"David" wrote in message
...
CLR wrote

Use a Helper column with the Name and a suffix of a sequential number,
like Brown,Charlie1 for row one of Charlie Browns data, then
Brown,Charlie2 for his second row, etc etc........then
Armstrong,Jack1....Armstrong,Jack2 for the next group etc
etc...........they don't have to be sorted by name, then sort on this
column and they sort as a group by name, and stay in the proper order
as well..........

Followup on initial setup: If I used formulas in helper column for the
first student like =A2&1, =A2&2, =A2&3, etc., where A2 held the first
student's name, selected those and drag-filled down, it took most of the
pain out.

--
David

CLR wrote

Followup on your followup <g
Although the concatenated suffix's must be in sequence for each group,
they need NOT start with 1 each time..........sooo, you could just use
another helper column with numbers sequenced from 1-XXXX, and then
concatenate the names and the respective row from this new column, so
it might read,
Brown,Charlie1....Brown,Charlie2......Armstrong,Ja ck12......Armstrong,J
ack13 , etc etc..........would make the initial setup easier, just
type in one formula and copy down................

Ok, I follow the first part (sequenced numbers in another helper column),
but then I get lost with 2nd (concatenate names and the respective row from
this new column).

Test data:
Col A = names followed by 3 rows of data in Col A, B & C
Col D = 1st helper column with ??? formula to copy down
Col E = 2nd helper oolumn with sequenced numbers

I'm lost.

--
David

I'm sorry David.........I thought you had gotten to the point where you had
only names in one column, with their data in the columns to the
right.........like
ColumnA................ColumnB.....ColumnC
Name........................Data1.........Data2
Brown,Charlie..........address1....address2
Brown,Charlie..........car1...........car2
Brown,Charlie..........wife1..........wife2
Brown,Charlie...........kids1.........kids2
Armstrong,Jack.........address1....address2
etc etc

And was hand-entering Brown,Charlie1....... Brown,Charlie2.........etc
Then Armstrong,Jack1.......Armstrong,Jack2........etc.. .......

I was only trying to point out that
Armstrong,Jack11....Armstrong,Jack12.....etc would sort just as well, as
long as the data within each group was sequential, it not have to start
again with 1,2,etc.......this way it could be concatenated in instead of
having to be typed in.............

If your data was in that order, you could have just put sequential numbers
in a helper column, starting with 101 at the top and incrementing down as
far as you have data(actually101 instead of 1 in case you have more than 9
rows for a name to prevent it sorting 1,10,2, etc)..........then in the next
helper column you could have CONCATENATED column A (the name) with the
sequential number in that helper column, and then copied this formula down.
This method will give you a unique name-number combination for each row,
sequenced within each group, and they will sort and stay together. If you
can get to that point on your own, that would probably be the easiest way to
be able to sort by name and keep the associated data together with the name.
I've done this with several thousand row databases with varying numbers of
data lines for each principal. It works good. If you cannot get to that
name/data configuration described above then send me a sample of your
worksheet ( to my home addy, not the newsgroup), and I will try to bust it
out for you. If you do, send enough lines as to be typical of the whole
thing........


Basically what I'm trying to describe is something like this........

NameA.....data.....data.....data.....101
NameA.....data.....data.....data.....102
NameA.....data.....data.....data.....103
NameK.....data.....data.....data.....104
NameK.....data.....data.....data.....105
NameK.....data.....data.....data.....106
NameB.....data.....data.....data.....107
NameB.....data.....data.....data.....108
NameB.....data.....data.....data.....109

Then in the column after the sequential numbers, concatenate the Name and
the sequential number so it reads NameA101, NameA102, NameA103, NameK104,
etc down that column........then sort on that column and NameB will follow
NameA, etc and the data will stay together......

I've also seen posts in these newsgroups whereby Macros were offered to
break data similar to yours out to all read on one row.........that might be
an alternative.......unfortunately, not my area of expertise, but if you are
interested in that approach, post back in a new thread and someone will no
doubt be able to help with that. Otherwise, we'll keep at this one until
you have something you can use.


Vaya con Dios,
Chuck, CABGx3




"David" wrote in message
...
CLR wrote

Followup on your followup <g
Although the concatenated suffix's must be in sequence for each group,
they need NOT start with 1 each time..........sooo, you could just use
another helper column with numbers sequenced from 1-XXXX, and then
concatenate the names and the respective row from this new column, so
it might read,
Brown,Charlie1....Brown,Charlie2......Armstrong,Ja ck12......Armstrong,J
ack13 , etc etc..........would make the initial setup easier, just
type in one formula and copy down................

Ok, I follow the first part (sequenced numbers in another helper column),
but then I get lost with 2nd (concatenate names and the respective row
from
this new column).

Test data:
Col A = names followed by 3 rows of data in Col A, B & C
Col D = 1st helper column with ??? formula to copy down
Col E = 2nd helper oolumn with sequenced numbers

I'm lost.

--
David

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" wrote in message news:uva%
Acceptable alternative: Have each student on their own page with sheets
named Lastname Firstname, sort those, and still be able to Subtotal in
cells on those sheets, then Grand Total on a separate sheet.

CLR wrote

I'm sorry David.........I thought you had gotten to the point where
you had only names in one column, with their data in the columns to
the right.........like
ColumnA................ColumnB.....ColumnC
Name........................Data1.........Data2
Brown,Charlie..........address1....address2
Brown,Charlie..........car1...........car2
Brown,Charlie..........wife1..........wife2
Brown,Charlie...........kids1.........kids2
Armstrong,Jack.........address1....address2
etc etc

And was hand-entering Brown,Charlie1....... Brown,Charlie2.........etc
Then Armstrong,Jack1.......Armstrong,Jack2........etc.. .......

I was only trying to point out that
Armstrong,Jack11....Armstrong,Jack12.....etc would sort just as well,
as long as the data within each group was sequential, it not have to
start again with 1,2,etc.......this way it could be concatenated in
instead of having to be typed in.............
Basically what I'm trying to describe is something like this........

NameA.....data.....data.....data.....101
NameA.....data.....data.....data.....102
NameA.....data.....data.....data.....103
NameK.....data.....data.....data.....104
NameK.....data.....data.....data.....105
NameK.....data.....data.....data.....106
NameB.....data.....data.....data.....107
NameB.....data.....data.....data.....108
NameB.....data.....data.....data.....109

Then in the column after the sequential numbers, concatenate the Name
and the sequential number so it reads NameA101, NameA102, NameA103,
NameK104, etc down that column........then sort on that column and
NameB will follow NameA, etc and the data will stay together......

Yeah, that makes sense if data was laid out as you describe. The sheet
layout has to remain as it is (school issued). My followup solution works
fine for me. Thanks for your help and offer.

--
David

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

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

David McRitchie wrote

Perhaps the example at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
might be helpful

I'll have a look.

I emailed an example based on sketchy details
you posted.

Sorry, I'll never get it. Address used here is bogus.

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.

All sheets (pages in my single sheet scenario) have same format, dates
are all weekdays in month, students take same classes.

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

studentname, class, date

Data is set up as described in my last post. Just wasn't enough room to
show 25 date (C:AA) and 3 name/classname/total (A:B & AB) columns in a
post.

I left out 3 titles for ColA and 1 Total in ColB, though:
Page 1
This: SUM Monthly Student1 class time
should be
Total Class Hrs SUM Monthly Student1 class time

Same for Page 2

Page X
Add this after last item
Grand Total SUM all monthly class hours for all students

I know Pivot Tables can be powerful tools, based on all the suggestions
to use them in NG's, but every time I've tried to experiment, I get
totally lost and confused by the results, probably because test data is
from current files not suitable for Pivot Tables.

I think by now I've thought through this thoroughly enough to set things
up the way I need and maintain the integrity of the original file. One
change I'll make is to use SUBTOTAL formulas in place of current SUM
formulas in ColB.

--
David