ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Sort pages? (https://www.excelbanter.com/excel-discussion-misc-queries/25743-sort-pages.html)

David May 11th 05 09:43 PM

Sort pages?
 
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

Dominic May 12th 05 12:22 AM

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


CLR May 12th 05 12:40 AM

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




Dave Peterson May 12th 05 01:39 AM

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

David May 12th 05 09:43 AM

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

David May 12th 05 10:00 AM

?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

David May 12th 05 10:12 AM

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

David May 12th 05 10:39 AM

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 May 12th 05 11:28 AM

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




David May 12th 05 10:46 PM

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


All times are GMT +1. The time now is 12:31 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
ExcelBanter.com