Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
sort source workbook data, maintain formulas in destination workb. | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
When I change my data, my formulas don't update the answers,why? | Excel Discussion (Misc queries) |