Sorting a portion of a worksheet

[NicoleS]

*Using Office 2007*

I am using the sort function to organize a list by date and anniversary
year. That works well.

Then I insert blank lines (for readability) between groups of dates
according to weeks on the calendar. No problem.

Next, I want to sort the names alphabetically within the anniversary year
and week. Each time it asks if I want to expand the selection. I say yes so
that all of the data (name, anniv. year, anniv. date, job title, job
location) stays together for each entry. Sometimes this works. Sometimes it
doesn't.
When it works, just the names of associates celebrating 10 year
anniversaries for week 3 are alphabetized (for example), then I alphabetize
the names of associates celebrating 20 year anniversaries).

When it doesn't work, excel disregards the blank lines that I placed and
alphabetizes my whole list - no longer separated by weeks of the month. (it
clumps them all together.)

i hope this makes sense. How can I get it expand the sort to JUST the
section that was highlighted?

 

[Steve Dunn]

Hi Nicole,

blank rows are a bad idea in any dataset - borders improve readability
without reducing usability, and you can use Conditional Formatting to apply
them automatically*.

However, to answer your question: rather than just selecting a vertical
range, you need to select all of the data you want to sort, making sure that
the active cell is within the column you want to sort by (tab along until
the white cell is there). Then perform your sort without expanding the
selection.


*The following formula can be used in Conditional Formatting, with a bottom
border, to distinguish between weeks of the year, assuming that you want the
first week to start on the first Monday and that column C contains the full
date:

=ROUNDUP(($C1-DATE(YEAR($C1),1,1)-WEEKDAY($C1,11))/7,0)<>ROUNDUP(($C2-DATE(YEAR($C2),1,1)-WEEKDAY($C2,11))/7,0)

Steve D.