when I data sort my formulas change
 

I have a fairly large spreadsheet that involves adding and deleting rows
based on bills paid to date. I am using a "sumif" stipulation on the
worksheet to add everything from this one vendor in this one department. I
would like to be able to add rows on the bottom, then sort the data. The
problem I'm having is that when I do this, the formulas get all mixed up.
For example is refering to a1:a5 before I sort. I add a line at the bottom
that pushes the first row down. The formula now refers to a2:a6. I would
like that formula to refer to a1:a6.

when I data sort my formulas change
 

Perhaps you need to use Absolute References in your formula.

For example
Perhaps =SUM(A1:A5)
Should be =SUM(A$1:A$5)

For more information:
Search Excel Help for "range reference"
Read this section: About cell and range references

Does that help?

If no....please provide more details about what you're doing and what's
going wrong.

***********
Regards,
Ron

XL2002, WinXP


"Andrea@Lackey" wrote:

I have a fairly large spreadsheet that involves adding and deleting rows
based on bills paid to date. I am using a "sumif" stipulation on the
worksheet to add everything from this one vendor in this one department. I
would like to be able to add rows on the bottom, then sort the data. The
problem I'm having is that when I do this, the formulas get all mixed up.
For example is refering to a1:a5 before I sort. I add a line at the bottom
that pushes the first row down. The formula now refers to a2:a6. I would
like that formula to refer to a1:a6.

when I data sort my formulas change
 

I will definitely try that and let you know if it works. Thanks for the advice!

"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)

For more information:
Search Excel Help for "range reference"
Read this section: About cell and range references

Does that help?

If no....please provide more details about what you're doing and what's
going wrong.

***********
Regards,
Ron

XL2002, WinXP


"Andrea@Lackey" wrote:

I have a fairly large spreadsheet that involves adding and deleting rows
based on bills paid to date. I am using a "sumif" stipulation on the
worksheet to add everything from this one vendor in this one department. I
would like to be able to add rows on the bottom, then sort the data. The
problem I'm having is that when I do this, the formulas get all mixed up.
For example is refering to a1:a5 before I sort. I add a line at the bottom
that pushes the first row down. The formula now refers to a2:a6. I would
like that formula to refer to a1:a6.

when I data sort my formulas change
 

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

"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)

For more information:
Search Excel Help for "range reference"
Read this section: About cell and range references

Does that help?

If no....please provide more details about what you're doing and what's
going wrong.

***********
Regards,
Ron

XL2002, WinXP


"Andrea@Lackey" wrote:

I have a fairly large spreadsheet that involves adding and deleting rows
based on bills paid to date. I am using a "sumif" stipulation on the
worksheet to add everything from this one vendor in this one department. I
would like to be able to add rows on the bottom, then sort the data. The
problem I'm having is that when I do this, the formulas get all mixed up.
For example is refering to a1:a5 before I sort. I add a line at the bottom
that pushes the first row down. The formula now refers to a2:a6. I would
like that formula to refer to a1:a6.

when I data sort my formulas change
 

Thanks for the feedback, Andrea....I'm glad I could help.

***********
Regards,
Ron

XL2002, WinXP


"Andrea@Lackey" wrote:


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

"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)

For more information:
Search Excel Help for "range reference"
Read this section: About cell and range references

Does that help?

If no....please provide more details about what you're doing and what's
going wrong.

***********
Regards,
Ron

XL2002, WinXP


"Andrea@Lackey" wrote:

I have a fairly large spreadsheet that involves adding and deleting rows
based on bills paid to date. I am using a "sumif" stipulation on the
worksheet to add everything from this one vendor in this one department. I
would like to be able to add rows on the bottom, then sort the data. The
problem I'm having is that when I do this, the formulas get all mixed up.
For example is refering to a1:a5 before I sort. I add a line at the bottom
that pushes the first row down. The formula now refers to a2:a6. I would
like that formula to refer to a1:a6.

when I data sort my formulas change
 

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.