Sorting affecting my data ranges

[ChuckF]

I have created data ranges in a particular worksheet. There are
formulas calculating those data ranges, but when the data is sorted a
different way, it obviously affects my formulas. Is there a way to
"lock" in those ranges, and have the ranges move with the sorted cells?

I think this makes sense...but if not, let me know.

 

[Jim May]

As regards sorting - generally, we're talking records
here... By design, when you select at the Menu, Data, Sort -- Excel
branches out from the activecell looking to establish the boundaries of a
table, which it will auto assign. SOMETIMES, it gets the table range
wrong, so that is why it's best <<sometimes>> to explicitly provide the
coordinates, or range. I usually BEFORE Sorting (particularly, if I'm in
doubt) Select/highlight the top-left-most-cell (which is my header/label
row) then paint downward and to the right to the last records, last field.
Now I'm ready to do the Date, Sort, assign the various parameters (making
sure the HasHeaderRow is checked)...
Doing all this assures me that my records stay together, whether fields are
constant values or formulas.

Hope this Helps,,
Jim May