I've been Googling all day on this one with no luck.

Excel 2003 spreadsheet with groups (sections) of rows formatted to
have different background colors to differentiate them visually which
are summed up in a summary section at the bottom of the sheet (Sum and
Percent Filled In formulas for each colored section). The sections do
NOT contain the same number of rows each, they vary!

Furthermore, these sections have different sort orders per section and
can be changed on the fly by the user by clicking buttons to change
the order of the colored sections (rows within a section remain the
same, the entire sections get swapped around though)... hidden columns
are used to contain the different sort orders possible for each
section.

The Selection.Sort method is used to re-order them.

When Selection.Sort is applied on the approriate column, it correctly
changes the order of the colored sections... but the formulas are no
longer correct as they aren't being updated automatically and are
still referencing the original cell locations which no longer match
the colored sections!

I've tried defining Range Names and using them in the summary formulas
to no avail... the range definitions do not update when Selection.Sort
is applied either apparently.

So how can I fix this problem? I have to have the different sections
and the sections have to be contiguous and different sizes (row wise)
with a sheet summary at the bottom. Is there a simple solution I'm
missing? Or do I have to write VBA code to programatically update all
the summary formulas to point to the new section locations (not fun,
IMHO)?

I notice that if I manually cut and paste the sections to different
locations the Range Name definitions do get automatically updated, no
matter where on the sheet I paste each section. Why does
Selection.Sort not update the Range Name definitions automatically?

Thanks for any help ya'll can provide! I'm at wits end here.

ps. I've a BS in Computer Science and have been doing Visual Basic,
Access and SQL Server develoment for some 15 years now... I've only
recently started seriously working in Excel though so the objects I'm
dealing with are a little new to me (ranges and worksheets and cells
instead of fields and tables and forms and the like).

- Michael