Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula - relative or absolute ref, keeps changing
Have a simple idea that is giving me fits.
Have a worksheet with numberic data and labels in rows and columns. Below each column I use a simple SUM() function which works fine, until I disturb a row of data. By disturb, what I mean is that if I move the top row of data down to the bottom of the array, highlight the entire data area, move that area up one row, I lose the correct references in the SUM() function. I'm just moving the data area, not the row containing my SUM() functions - that stays put. IOW, the SUM() function is in row 12, the data area extends from row 1 through row 10, I move row 1 to row 11 and then move everything from row 2 through row 11, back up one row. I've tried using relative and absolute addressing but that SUM() formula somehow keeps adjusting based on what it sees me moving and giving me an incorrect result. How do I make the formula "static", or to just tell it "sum the numbers above and don't watch what's moving around, dog-gone it!". Thanks for any advice here. |
#2
|
|||
|
|||
To use absolute addressing try =SUM($A$1:$A$10)
"Hoib" wrote: Have a simple idea that is giving me fits. Have a worksheet with numberic data and labels in rows and columns. Below each column I use a simple SUM() function which works fine, until I disturb a row of data. By disturb, what I mean is that if I move the top row of data down to the bottom of the array, highlight the entire data area, move that area up one row, I lose the correct references in the SUM() function. I'm just moving the data area, not the row containing my SUM() functions - that stays put. IOW, the SUM() function is in row 12, the data area extends from row 1 through row 10, I move row 1 to row 11 and then move everything from row 2 through row 11, back up one row. I've tried using relative and absolute addressing but that SUM() formula somehow keeps adjusting based on what it sees me moving and giving me an incorrect result. How do I make the formula "static", or to just tell it "sum the numbers above and don't watch what's moving around, dog-gone it!". Thanks for any advice here. |
#3
|
|||
|
|||
You can use the Indirect function: =SUM(INDIRECT("A1:A10"))
Hoib wrote: Have a simple idea that is giving me fits. Have a worksheet with numberic data and labels in rows and columns. Below each column I use a simple SUM() function which works fine, until I disturb a row of data. By disturb, what I mean is that if I move the top row of data down to the bottom of the array, highlight the entire data area, move that area up one row, I lose the correct references in the SUM() function. I'm just moving the data area, not the row containing my SUM() functions - that stays put. IOW, the SUM() function is in row 12, the data area extends from row 1 through row 10, I move row 1 to row 11 and then move everything from row 2 through row 11, back up one row. I've tried using relative and absolute addressing but that SUM() formula somehow keeps adjusting based on what it sees me moving and giving me an incorrect result. How do I make the formula "static", or to just tell it "sum the numbers above and don't watch what's moving around, dog-gone it!". Thanks for any advice here. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
Thank you bigwheel. I actually had tried this method too with the same
result. The expression $A$1:$A$10 also "flexes" or changes as I move the top row to the bottom yielding an unwanted result - the moved row is excluded even though I move the entire 10 row array back into position. I will say, however, the INDIRECT() function spec'd in a subsequent post by Debra, is apparently one answer to this. I'm also sure there are probably a hundred different techniques to get this done. So, again, I thank you for your contribution. "bigwheel" wrote in message ... To use absolute addressing try =SUM($A$1:$A$10) "Hoib" wrote: Have a simple idea that is giving me fits. Have a worksheet with numberic data and labels in rows and columns. Below each column I use a simple SUM() function which works fine, until I disturb a row of data. By disturb, what I mean is that if I move the top row of data down to the bottom of the array, highlight the entire data area, move that area up one row, I lose the correct references in the SUM() function. I'm just moving the data area, not the row containing my SUM() functions - that stays put. IOW, the SUM() function is in row 12, the data area extends from row 1 through row 10, I move row 1 to row 11 and then move everything from row 2 through row 11, back up one row. I've tried using relative and absolute addressing but that SUM() formula somehow keeps adjusting based on what it sees me moving and giving me an incorrect result. How do I make the formula "static", or to just tell it "sum the numbers above and don't watch what's moving around, dog-gone it!". Thanks for any advice here. |
#5
|
|||
|
|||
Perfect! It works as advertised! Thanks a lot!
"Debra Dalgleish" wrote in message ... You can use the Indirect function: =SUM(INDIRECT("A1:A10")) Hoib wrote: Have a simple idea that is giving me fits. Have a worksheet with numberic data and labels in rows and columns. Below each column I use a simple SUM() function which works fine, until I disturb a row of data. By disturb, what I mean is that if I move the top row of data down to the bottom of the array, highlight the entire data area, move that area up one row, I lose the correct references in the SUM() function. I'm just moving the data area, not the row containing my SUM() functions - that stays put. IOW, the SUM() function is in row 12, the data area extends from row 1 through row 10, I move row 1 to row 11 and then move everything from row 2 through row 11, back up one row. I've tried using relative and absolute addressing but that SUM() formula somehow keeps adjusting based on what it sees me moving and giving me an incorrect result. How do I make the formula "static", or to just tell it "sum the numbers above and don't watch what's moving around, dog-gone it!". Thanks for any advice here. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
|
|||
|
|||
You're welcome!
Hoib wrote: Perfect! It works as advertised! Thanks a lot! "Debra Dalgleish" wrote in message ... You can use the Indirect function: =SUM(INDIRECT("A1:A10")) Hoib wrote: Have a simple idea that is giving me fits. Have a worksheet with numberic data and labels in rows and columns. Below each column I use a simple SUM() function which works fine, until I disturb a row of data. By disturb, what I mean is that if I move the top row of data down to the bottom of the array, highlight the entire data area, move that area up one row, I lose the correct references in the SUM() function. I'm just moving the data area, not the row containing my SUM() functions - that stays put. IOW, the SUM() function is in row 12, the data area extends from row 1 through row 10, I move row 1 to row 11 and then move everything from row 2 through row 11, back up one row. I've tried using relative and absolute addressing but that SUM() formula somehow keeps adjusting based on what it sees me moving and giving me an incorrect result. How do I make the formula "static", or to just tell it "sum the numbers above and don't watch what's moving around, dog-gone it!". -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Preform alphabetical sort without formula in cell changing | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Changing the Color of a Word in Formula | Excel Worksheet Functions | |||
Relative goes absolute between worksheets in data sort! | Excel Discussion (Misc queries) | |||
what is the formula for changing the same cell on different sheet. | Excel Worksheet Functions |