Andrea@Lackey wrote:
"Ron Coderre" wrote:
Perhaps you need to use Absolute References in your formula.
For example
Perhaps =SUM(A1:A5)
Should be =SUM(A$1:A$5)

That did it!!!! I'm so excited, it finally works!!! THANK YOU!!

It is great that Ron's solution worked well for you. But be forewarned
that there are situations (I believe) where there is no solution. Yes,
sorting data might screw up references to that data (sigh). Caveat
emptor!

Here is a simple example: A1=1, A2=10, B1=A1/A2 (or any combination of
$-references). If our intent is for B1 to remain 0.1 after sorting
A1:A2 in reverse order (descending), I do not believe it will work.

These situations do occur in real life. I had a balance sheet where I
separated assets by category, and the value of one asset in one
category was the total account value less the value of an asset in
another category. (I was separating cash and securities in a single
account.) When I sorted each category based on the holding institution
name, the derived asset value got screwed up -- as did the category
subtotal, of course.

In fact, I suspect that Ron's solution worked for you only because the
range you sorted was the same or a subset of the range references. For
example, if you had SUM(A$1:A$3) and you sorted A1:A5, I suspect you
would not have been so "excited" about the results.